<?xml version="1.0" encoding="utf-8"?>
<ArrayOfLPQuery xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <LPQuery>
    <QueryName>ActiveSync Proxy: Exchange 2013, Find OPTIONS requests with errors</QueryName>
    <QueryDescription>Exchange 2013: Finds any EAS HTTP proxy requests that either contain an error or an HTTP response that isn't 200 OK.</QueryDescription>
    <QueryData>/*  Exchange 2013 - ActiveSync Proxy: Find OPTIONS requests that contain error responses */

SELECT TOP 1000 * FROM '[LOGFILEPATH]'
WHERE ErrorCode IS NOT NULL
OR TO_INT(HttpStatus) &gt; 299
AND Method LIKE '%OPTIONS%' </QueryData>
    <QueryID>95c8345d-7224-4328-8144-2b81742e9825</QueryID>
    <LogType>EELXLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-03T20:53:29.6937329-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync Proxy: Exchange 2013, Find OPTIONS requests with errors</QueryName>
    <QueryDescription>Exchange 2013: Finds any EAS HTTP proxy requests that either contain an error or an HTTP response that isn't 200 OK.</QueryDescription>
    <QueryData>/*  Exchange 2013 - ActiveSync Proxy: Find OPTIONS requests that contain error responses */

SELECT TOP 1000 * FROM '[LOGFILEPATH]'
WHERE ErrorCode IS NOT NULL
OR TO_INT(HttpStatus) &gt; 299
AND Method LIKE '%OPTIONS%' </QueryData>
    <QueryID>95c8345d-7224-4328-8144-2b81742e9825</QueryID>
    <LogType>EELXLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-03T20:53:29.6937329-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync Proxy: Find Field Names</QueryName>
    <QueryDescription>Exchange 2013: Finds the field/column names for the EAS HTTP Proxy logs for Exchange 2013. </QueryDescription>
    <QueryData>/*  Exchange 2013 - ActiveSync Proxy: Display Field Names */
 

SELECT TOP 0 * FROM '[LOGFILEPATH]'


/* 

Right-click anywhere in the results window above after running the query, then choose Copy to copy fieldnames to clipboard. 
This a simple method for gathering fieldnames for logs you may not be familiar with when writing your own queries.
For example, the fields below were gathered after running the above query:


Filename, RowNumber, DateTime, RequestId, MajorVersion, MinorVersion, BuildVersion, RevisionVersion, ClientRequestId, 
Protocol, UrlStem, ProtocolAction, AuthenticationType, IsAuthenticated, AuthenticatedUser, Organization, AnchorMailbox, 
UserAgent, ClientIpAddress, ServerHostName, HttpStatus, BackEndStatus, ErrorCode, Method, ProxyAction, TargetServer, 
TargetServerVersion, RoutingType, RoutingHint, BackEndCookie, ServerLocatorHost, ServerLocatorLatency, RequestBytes, 
ResponseBytes, TargetOutstandingRequests, AuthModulePerfContext, HttpPipelineLatency, CalculateTargetBackEndLatency, 
GlsLatencyBreakup, TotalGlsLatency, AccountForestLatencyBreakup, TotalAccountForestLatency, ResourceForestLatencyBreakup, 
TotalResourceForestLatency, ADLatency, ActivityContextLifeTime, ModuleToHandlerSwitching, FirstResponseByteReceived, 
ProxyTime, RequestHandlerLatency, HandlerToModuleSwitching, HttpProxyOverhead, RoutingLatency, NetworkLatency, 
BackendProcessingLatency, TotalRequestTime, UrlQuery, BackEndGenericInfo, GenericInfo, GenericErrors

^ Now that we know the field names we can use them to construct our own query:

SELECT TOP 500 FROM '[LOGFILEPATH]' 
WHERE TotalRequestTime &gt; 500


*/</QueryData>
    <QueryID>bdcfba38-7130-4ce7-8429-39a4a97bfead</QueryID>
    <LogType>EELXLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-03T20:35:58.9194785-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync Report</QueryName>
    <QueryDescription>MAS detailed ActiveSync usage report</QueryDescription>
    <QueryData>SELECT
   TO_LOWERCASE (cs-username)    AS User,
   MyDeviceId                   AS DeviceId,
   MyDeviceType                     AS DeviceType,
   cs(User-Agent)                  AS User-Agent,
   COUNT(*)                          AS Hits,
   SUM (MyPing)      AS Ping,
   SUM (MySync)                        AS Sync,
   SUM (MyFolderSync)                  AS FolderSync,
   SUM (MySendMail)                    AS SendMail,
   SUM (MySmartReply)                  AS SmartReply,
   SUM (MyMeetingResponse)          AS MeetingResponse,
   SUM (MyGetAttachment)              AS GetAttachment,
   SUM (MySmartForward)    AS SmartForward,
   SUM (MyGetHierarchy)    AS GetHierarchy,
   SUM (MyCreateCollection)   AS CreateCollection,
   SUM (MyDeleteCollection)   AS DeleteCollection,
   SUM (MyMoveCollection)    AS MoveCollection,
   SUM (MyFolderCreate)    AS FolderCreate,
   SUM (MyFolderDelete)    AS FolderDelete,
   SUM (MyFolderUpdate)    AS FolderUpdate,
   SUM (MyMoveItems)     AS MoveItems,
   SUM (MyGetItemEstimate)    AS GetItemEstimate,
   SUM (MySearch)      AS Search,
   SUM (MySettings)     AS Settings,
   SUM (MyItemOperations)    AS ItemOperations,
   SUM (MyProvision)     AS Provision,
   SUM (MyResolveRecipients)   AS ResolveRecipients,
   SUM (MyValidateCert)    AS ValidateCert,
   DIV(MAX(time-taken),1000)   AS LongestRequestTime(s),DIV(AVG(time-taken),1000)   AS AVGRequestTime(s),
   SUM (MyInvalidContent)    AS InvalidContent,
   SUM (MyServerError)     AS ServerError,
   SUM (MyServerErrorRetryLater)  AS ServerErrorRetryLater,
   SUM (MyMailboxQuotaExceeded)  AS MailboxQuotaExceeded,
   SUM (MyDeviceIsBlockedForThisUser) AS DeviceIsBlockedForThisUser,
   SUM (MyAccessDenied)    AS AccessDenied,
   SUM (MySyncStateNotFound)   AS SyncStateNotFound,
   SUM (MyDeviceNotFullyProvisionable) AS DeviceNotFullyProvisionable,
   SUM (MyDeviceNotProvisioned)  AS DeviceNotProvisioned,
   SUM (MyItemNotFound)    AS ItemNotFound,
   SUM (ADD(MyDisabledForSyncCnt1,MyDisabledForSyncCnt2))                      AS UserDisabledForSync,
   SUM (MyTooManyJobsQueued)   AS TooManyJobsQueued,
   SUM (MyOverBudget)     AS OverBudget,
   SUM (MyIIS_5xx)      AS IIS_5xx,
   SUM (MyIIS_4xx)      AS IIS_4xx,
   SUM (MyIIS_503)      AS IIS_503,
   SUM (MyIIS_507)      AS IIS_507,
   SUM (MyIIS_409)      AS IIS_409,
   SUM (MyIIS_451)      AS IIS_451

USING
   EXTRACT_VALUE(cs-uri-query,'DeviceType') AS MyDeviceType,
   EXTRACT_VALUE(cs-uri-query,'DeviceId') AS MyDeviceId,
   EXTRACT_VALUE(cs-uri-query,'User-Agent') AS MyUser-Agent,
   EXTRACT_VALUE(cs-uri-query,'Cmd') AS MyCmd,
   EXTRACT_VALUE(cs-uri-query,'Log') AS MyLog,

   SUBSTR(TO_STRING(sc-status),0,1) AS StatusCode,

   /* Getting any error's that might be in MyLog */
   SUBSTR (MyLog, ADD (INDEX_OF (MyLog, 'Error:'), 6),
   INDEX_OF (SUBSTR(MyLog, ADD (INDEX_OF (MyLog, 'Error:'), 6)), '_')) AS MyLogError,

   /* Detect if ActiveSync is disabled for User */
   /* Exchange 2003 */
            CASE EXTRACT_TOKEN(MyLog,0,':')
                                    WHEN 'VNATNASNC' THEN 1
                                    ELSE 0
            END AS MyDisabledForSyncCnt1,

   /* Exchange 2010 */
            CASE MyLogError
                                    WHEN 'UserDisabledForSync' THEN 1
                                    ELSE 0
            END AS MyDisabledForSyncCnt2,

   /* END -- Detecting if ActiveSync is disabled for User */

            CASE MyLogError
                                    WHEN 'OverBudget' THEN 1
                                    ELSE 0
            END AS MyOverBudget,

            CASE MyLogError
                                    WHEN 'TooManyJobsQueued' THEN 1
                                    ELSE 0
            END AS MyTooManyJobsQueued,

            CASE MyLogError
                                    WHEN 'InvalidContent' THEN 1
                                    ELSE 0
            END AS MyInvalidContent,

   CASE MyLogError
                                    WHEN 'ServerError' THEN 1
                                    ELSE 0
            END AS MyServerError,

   CASE MyLogError
                                    WHEN 'ServerErrorRetryLater' THEN 1
                                    ELSE 0
            END AS MyServerErrorRetryLater,

   CASE MyLogError
                                    WHEN 'MailboxQuotaExceeded' THEN 1
                                    ELSE 0
            END AS MyMailboxQuotaExceeded,

   CASE MyLogError
                                    WHEN 'DeviceIsBlockedForThisUser' THEN 1
                                    ELSE 0
            END AS MyDeviceIsBlockedForThisUser,

   CASE MyLogError
                                    WHEN 'AccessDenied' THEN 1
                                    ELSE 0
            END AS MyAccessDenied,

   CASE MyLogError
                                    WHEN 'SyncStateNotFound' THEN 1
                                    ELSE 0
            END AS MySyncStateNotFound,

   CASE MyLogError
                                    WHEN 'DeviceNotFullyProvisionable' THEN 1
                                    ELSE 0
            END AS MyDeviceNotFullyProvisionable,

   CASE MyLogError
                                    WHEN 'DeviceNotProvisioned' THEN 1
                                    ELSE 0
            END AS MyDeviceNotProvisioned,

   CASE MyLogError
                                    WHEN 'ItemNotFound' THEN 1
                                    ELSE 0
            END AS MyItemNotFound,

   CASE StatusCode
      WHEN '5' THEN 1
      ELSE 0
   END AS MyIIS_5xx,

   CASE StatusCode
      WHEN '4' THEN 1
      ELSE 0
   END AS MyIIS_4xx,

   CASE TO_STRING(sc-status)
      WHEN '503' THEN 1
      ELSE 0
   END AS MyIIS_503,

   CASE TO_STRING(sc-status)
      WHEN '507' THEN 1
      ELSE 0
   END AS MyIIS_507,

   CASE TO_STRING(sc-status)
      WHEN '409' THEN 1
      ELSE 0
   END AS MyIIS_409,

   CASE TO_STRING(sc-status)
      WHEN '451' THEN 1
      ELSE 0
   END AS MyIIS_451,

            CASE MyCmd
                        WHEN 'Sync' THEN 1
                        ELSE 0
            END AS MySync,

            CASE MyCmd
                        WHEN 'Ping' THEN 1
                        ELSE 0
            END AS MyPing,

            CASE MyCmd
                        WHEN 'SendMail' THEN 1
                        ELSE 0
            END AS MySendMail,

            CASE MyCmd
                        WHEN 'SmartReply' THEN 1
                        ELSE 0
            END AS MySmartReply,

            CASE MyCmd
                        WHEN 'MeetingResponse' THEN 1
                        ELSE 0
            END AS MyMeetingResponse,

            CASE MyCmd
                        WHEN 'GetAttachment' THEN 1
                        ELSE 0
            END AS MyGetAttachment,

            CASE MyCmd
                        WHEN 'FolderSync' THEN 1
                        ELSE 0

            END AS MyFolderSync,

   CASE MyCmd
                        WHEN 'SmartFoward' THEN 1
                        ELSE 0
            END AS MySmartForward,

   CASE MyCmd
                        WHEN 'GetHierarchy' THEN 1
                        ELSE 0
            END AS MyGetHierarchy,

   CASE MyCmd
                        WHEN 'CreateCollection' THEN 1
                        ELSE 0
            END AS MyCreateCollection,

   CASE MyCmd
                        WHEN 'DeleteCollection' THEN 1
                        ELSE 0
            END AS MyDeleteCollection,

   CASE MyCmd
                        WHEN 'MoveCollection' THEN 1
                        ELSE 0
            END AS MyMoveCollection,

   CASE MyCmd
                        WHEN 'FolderCreate' THEN 1
                        ELSE 0
            END AS MyFolderCreate,

   CASE MyCmd
                        WHEN 'FolderDelete' THEN 1
                        ELSE 0
            END AS MyFolderDelete,

   CASE MyCmd
                        WHEN 'FolderUpdate' THEN 1
                        ELSE 0
            END AS MyFolderUpdate,

   CASE MyCmd
                        WHEN 'MoveItems' THEN 1
                        ELSE 0
            END AS MyMoveItems,

   CASE MyCmd
                        WHEN 'GetItemEstimate' THEN 1
                        ELSE 0
            END AS MyGetItemEstimate,

   CASE MyCmd
                        WHEN 'Search' THEN 1
                        ELSE 0
            END AS MySearch,

   CASE MyCmd
                        WHEN 'Settings' THEN 1
                        ELSE 0
            END AS MySettings,

   CASE MyCmd
                        WHEN 'ItemOperations' THEN 1
                        ELSE 0
            END AS MyItemOperations,

   CASE MyCmd
                        WHEN 'Provision' THEN 1
                        ELSE 0
            END AS MyProvision,

   CASE MyCmd
                        WHEN 'ResolveRecipients' THEN 1
                        ELSE 0
            END AS MyResolveRecipients,

   CASE MyCmd
                        WHEN 'ValidateCert' THEN 1
                        ELSE 0
            END AS MyValidateCert


                    FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%/Microsoft-Server-ActiveSync%'
GROUP BY User,DeviceType,DeviceId,User-Agent
ORDER BY Hits DESC
</QueryData>
    <QueryID>91930a9f-9680-47d2-8341-b68fac8c6417</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync Report [Top 20]</QueryName>
    <QueryDescription>MAS detailed ActiveSync usage report for the top 20 consumers</QueryDescription>
    <QueryData>SELECT TOP 20
   TO_LOWERCASE (cs-username)    AS User,
   MyDeviceId                   AS DeviceId,
   MyDeviceType                     AS DeviceType,
   cs(User-Agent)                  AS User-Agent,
   COUNT(*)                          AS Hits,
   SUM (MyPing)      AS Ping,
   SUM (MySync)                        AS Sync,
   SUM (MyFolderSync)                  AS FolderSync,
   SUM (MySendMail)                    AS SendMail,
   SUM (MySmartReply)                  AS SmartReply,
   SUM (MyMeetingResponse)          AS MeetingResponse,
   SUM (MyGetAttachment)              AS GetAttachment,
   SUM (MySmartForward)    AS SmartForward,
   SUM (MyGetHierarchy)    AS GetHierarchy,
   SUM (MyCreateCollection)   AS CreateCollection,
   SUM (MyDeleteCollection)   AS DeleteCollection,
   SUM (MyMoveCollection)    AS MoveCollection,
   SUM (MyFolderCreate)    AS FolderCreate,
   SUM (MyFolderDelete)    AS FolderDelete,
   SUM (MyFolderUpdate)    AS FolderUpdate,
   SUM (MyMoveItems)     AS MoveItems,
   SUM (MyGetItemEstimate)    AS GetItemEstimate,
   SUM (MySearch)      AS Search,
   SUM (MySettings)     AS Settings,
   SUM (MyItemOperations)    AS ItemOperations,
   SUM (MyProvision)     AS Provision,
   SUM (MyResolveRecipients)   AS ResolveRecipients,
   SUM (MyValidateCert)    AS ValidateCert,
   DIV(MAX(time-taken),1000)   AS LongestRequestTime(s),DIV(AVG(time-taken),1000)   AS AVGRequestTime(s),
   SUM (MyInvalidContent)    AS InvalidContent,
   SUM (MyServerError)     AS ServerError,
   SUM (MyServerErrorRetryLater)  AS ServerErrorRetryLater,
   SUM (MyMailboxQuotaExceeded)  AS MailboxQuotaExceeded,
   SUM (MyDeviceIsBlockedForThisUser) AS DeviceIsBlockedForThisUser,
   SUM (MyAccessDenied)    AS AccessDenied,
   SUM (MySyncStateNotFound)   AS SyncStateNotFound,
   SUM (MyDeviceNotFullyProvisionable) AS DeviceNotFullyProvisionable,
   SUM (MyDeviceNotProvisioned)  AS DeviceNotProvisioned,
   SUM (MyItemNotFound)    AS ItemNotFound,
   SUM (ADD(MyDisabledForSyncCnt1,MyDisabledForSyncCnt2))                      AS UserDisabledForSync,
   SUM (MyTooManyJobsQueued)   AS TooManyJobsQueued,
   SUM (MyOverBudget)     AS OverBudget,
   SUM (MyIIS_5xx)      AS IIS_5xx,
   SUM (MyIIS_4xx)      AS IIS_4xx,
   SUM (MyIIS_503)      AS IIS_503,
   SUM (MyIIS_507)      AS IIS_507,
   SUM (MyIIS_409)      AS IIS_409,
   SUM (MyIIS_451)      AS IIS_451

USING
   EXTRACT_VALUE(cs-uri-query,'DeviceType') AS MyDeviceType,
   EXTRACT_VALUE(cs-uri-query,'DeviceId') AS MyDeviceId,
   EXTRACT_VALUE(cs-uri-query,'User-Agent') AS MyUser-Agent,
   EXTRACT_VALUE(cs-uri-query,'Cmd') AS MyCmd,
   EXTRACT_VALUE(cs-uri-query,'Log') AS MyLog,

   SUBSTR(TO_STRING(sc-status),0,1) AS StatusCode,

   /* Getting any error's that might be in MyLog */
   SUBSTR (MyLog, ADD (INDEX_OF (MyLog, 'Error:'), 6),
   INDEX_OF (SUBSTR(MyLog, ADD (INDEX_OF (MyLog, 'Error:'), 6)), '_')) AS MyLogError,

   /* Detect if ActiveSync is disabled for User */
   /* Exchange 2003 */
            CASE EXTRACT_TOKEN(MyLog,0,':')
                                    WHEN 'VNATNASNC' THEN 1
                                    ELSE 0
            END AS MyDisabledForSyncCnt1,

   /* Exchange 2010 */
            CASE MyLogError
                                    WHEN 'UserDisabledForSync' THEN 1
                                    ELSE 0
            END AS MyDisabledForSyncCnt2,

   /* END -- Detecting if ActiveSync is disabled for User */

            CASE MyLogError
                                    WHEN 'OverBudget' THEN 1
                                    ELSE 0
            END AS MyOverBudget,

            CASE MyLogError
                                    WHEN 'TooManyJobsQueued' THEN 1
                                    ELSE 0
            END AS MyTooManyJobsQueued,

            CASE MyLogError
                                    WHEN 'InvalidContent' THEN 1
                                    ELSE 0
            END AS MyInvalidContent,

   CASE MyLogError
                                    WHEN 'ServerError' THEN 1
                                    ELSE 0
            END AS MyServerError,

   CASE MyLogError
                                    WHEN 'ServerErrorRetryLater' THEN 1
                                    ELSE 0
            END AS MyServerErrorRetryLater,

   CASE MyLogError
                                    WHEN 'MailboxQuotaExceeded' THEN 1
                                    ELSE 0
            END AS MyMailboxQuotaExceeded,

   CASE MyLogError
                                    WHEN 'DeviceIsBlockedForThisUser' THEN 1
                                    ELSE 0
            END AS MyDeviceIsBlockedForThisUser,

   CASE MyLogError
                                    WHEN 'AccessDenied' THEN 1
                                    ELSE 0
            END AS MyAccessDenied,

   CASE MyLogError
                                    WHEN 'SyncStateNotFound' THEN 1
                                    ELSE 0
            END AS MySyncStateNotFound,

   CASE MyLogError
                                    WHEN 'DeviceNotFullyProvisionable' THEN 1
                                    ELSE 0
            END AS MyDeviceNotFullyProvisionable,

   CASE MyLogError
                                    WHEN 'DeviceNotProvisioned' THEN 1
                                    ELSE 0
            END AS MyDeviceNotProvisioned,

   CASE MyLogError
                                    WHEN 'ItemNotFound' THEN 1
                                    ELSE 0
            END AS MyItemNotFound,

   CASE StatusCode
      WHEN '5' THEN 1
      ELSE 0
   END AS MyIIS_5xx,

   CASE StatusCode
      WHEN '4' THEN 1
      ELSE 0
   END AS MyIIS_4xx,

   CASE TO_STRING(sc-status)
      WHEN '503' THEN 1
      ELSE 0
   END AS MyIIS_503,

   CASE TO_STRING(sc-status)
      WHEN '507' THEN 1
      ELSE 0
   END AS MyIIS_507,

   CASE TO_STRING(sc-status)
      WHEN '409' THEN 1
      ELSE 0
   END AS MyIIS_409,

   CASE TO_STRING(sc-status)
      WHEN '451' THEN 1
      ELSE 0
   END AS MyIIS_451,

            CASE MyCmd
                        WHEN 'Sync' THEN 1
                        ELSE 0
            END AS MySync,

            CASE MyCmd
                        WHEN 'Ping' THEN 1
                        ELSE 0
            END AS MyPing,

            CASE MyCmd
                        WHEN 'SendMail' THEN 1
                        ELSE 0
            END AS MySendMail,

            CASE MyCmd
                        WHEN 'SmartReply' THEN 1
                        ELSE 0
            END AS MySmartReply,

            CASE MyCmd
                        WHEN 'MeetingResponse' THEN 1
                        ELSE 0
            END AS MyMeetingResponse,

            CASE MyCmd
                        WHEN 'GetAttachment' THEN 1
                        ELSE 0
            END AS MyGetAttachment,

            CASE MyCmd
                        WHEN 'FolderSync' THEN 1
                        ELSE 0

            END AS MyFolderSync,

   CASE MyCmd
                        WHEN 'SmartFoward' THEN 1
                        ELSE 0
            END AS MySmartForward,

   CASE MyCmd
                        WHEN 'GetHierarchy' THEN 1
                        ELSE 0
            END AS MyGetHierarchy,

   CASE MyCmd
                        WHEN 'CreateCollection' THEN 1
                        ELSE 0
            END AS MyCreateCollection,

   CASE MyCmd
                        WHEN 'DeleteCollection' THEN 1
                        ELSE 0
            END AS MyDeleteCollection,

   CASE MyCmd
                        WHEN 'MoveCollection' THEN 1
                        ELSE 0
            END AS MyMoveCollection,

   CASE MyCmd
                        WHEN 'FolderCreate' THEN 1
                        ELSE 0
            END AS MyFolderCreate,

   CASE MyCmd
                        WHEN 'FolderDelete' THEN 1
                        ELSE 0
            END AS MyFolderDelete,

   CASE MyCmd
                        WHEN 'FolderUpdate' THEN 1
                        ELSE 0
            END AS MyFolderUpdate,

   CASE MyCmd
                        WHEN 'MoveItems' THEN 1
                        ELSE 0
            END AS MyMoveItems,

   CASE MyCmd
                        WHEN 'GetItemEstimate' THEN 1
                        ELSE 0
            END AS MyGetItemEstimate,

   CASE MyCmd
                        WHEN 'Search' THEN 1
                        ELSE 0
            END AS MySearch,

   CASE MyCmd
                        WHEN 'Settings' THEN 1
                        ELSE 0
            END AS MySettings,

   CASE MyCmd
                        WHEN 'ItemOperations' THEN 1
                        ELSE 0
            END AS MyItemOperations,

   CASE MyCmd
                        WHEN 'Provision' THEN 1
                        ELSE 0
            END AS MyProvision,

   CASE MyCmd
                        WHEN 'ResolveRecipients' THEN 1
                        ELSE 0
            END AS MyResolveRecipients,

   CASE MyCmd
                        WHEN 'ValidateCert' THEN 1
                        ELSE 0
            END AS MyValidateCert


                    FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%/Microsoft-Server-ActiveSync%'
GROUP BY User,DeviceType,DeviceId,User-Agent
ORDER BY Hits DESC
</QueryData>
    <QueryID>8019fbe6-c749-45b3-81a2-80864b70cd93</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: 500x HTTP /3 Minutes</QueryName>
    <QueryDescription>Finds MAS 500x errors and breaks into 3 minute blocks</QueryDescription>
    <QueryData>SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 180) AS threemin, 
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir,
	sc-status as HTTPstatus
INTO '[OUTFILEPATH]\EWSHitsPerHourWithError503.csv'
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '/Microsoft-server-Activesync' AND sc-status = '503'
GROUP BY threemin, Vdir, HTTPstatus
ORDER BY threemin
</QueryData>
    <QueryID>d13bc572-447f-45ac-9192-9ebdc46b4a22</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Apple Device/iOS Version Report</QueryName>
    <QueryDescription>Retrieve all users along with their apple devices + iOS versions connecting to Exchange ActiveSync</QueryDescription>
    <QueryData>/*
Retreive all users with their apple devices and iOS versions connecting to Exchange Active Sync.
Output:
   User
   Device
   iOS version
Latest version for/of IOS see e.g. http://www.enterpriseios.com/wiki/UserAgent
*/
SELECT
	TO_UPPERCASE(cs-username) AS User, 
	CASE EXTRACT_TOKEN(cs(user-agent),0,'/')
		WHEN 'Apple-iPhone' THEN 'iPhone, 3G or 3GS'
		WHEN 'Apple-iPhone1C2' THEN 'iPhone 3G'
		WHEN 'Apple-iPhone2C1' THEN 'iPhone 3GS'
		WHEN 'Apple-iPhone3C1' THEN 'iPhone 4'
		WHEN 'Apple-iPhone3C3' THEN 'iPhone 4 CDMA'
		WHEN 'Apple-iPhone4C1' THEN 'iPhone 4S'
		WHEN 'Apple-iPhone5C1' THEN 'iPhone 5 GSM'
		WHEN 'Apple-iPhone5C2' THEN 'iPhone 5 CDMA'
		WHEN 'Apple-iPod' THEN 'iPod touch'
		WHEN 'Apple-iPod2C1' THEN 'iPod touch 2nd generation'
		WHEN 'Apple-iPod3C1' THEN 'iPod touch 3rd generation'
		WHEN 'Apple-iPod4C1' THEN 'iPod touch 4th generation'
		WHEN 'Apple-iPod5C1' THEN 'iPod touch 5th generation'
		WHEN 'Apple-iPad' THEN 'iPad'
		WHEN 'Apple-iPad1C1' THEN 'iPad'
		WHEN 'Apple-iPad2C1' THEN 'iPad 2 WiFi'
		WHEN 'Apple-iPad2C2' THEN 'iPad 2 WiFi + 3G GSM'
		WHEN 'Apple-iPad2C3' THEN 'iPad 2 WiFi + 3G CDMA'
		WHEN 'Apple-iPad3C1' THEN 'iPad (3rd generation) WiFi'
		WHEN 'Apple-iPad3C2' THEN 'iPad (3rd generation) WiFi + 4G Verizon / International'
		WHEN 'Apple-iPad3C3' THEN 'iPad (3rd generation) WiFi + 4G AT&amp;T / International'	END AS Device,
	CASE cs(user-agent)
		/* iPhone */
		WHEN 'Apple-iPhone/501.347' THEN '2.0'
		WHEN 'Apple-iPhone/501.347' THEN '2.0' 
		WHEN 'Apple-iPhone/502.108' THEN '2.0.1' 
		WHEN 'Apple-iPhone/503.001' THEN '2.0.2' 
		WHEN 'Apple-iPhone/505.138' THEN '2.1.1' 
		WHEN 'Apple-iPhone/506.136' THEN '2.1' 
		WHEN 'Apple-iPhone/507.077' THEN '2.2' 
		WHEN 'Apple-iPhone/508.11' THEN '2.2.1' 
		WHEN 'Apple-iPhone/701.341' THEN '3.0' 
		WHEN 'Apple-iPhone/701.400' THEN '3.0.1' 
		WHEN 'Apple-iPhone/703.144' THEN '3.1' 
		WHEN 'Apple-iPhone/704.011' THEN '3.1.2' 
		WHEN 'Apple-iPhone/705.018' THEN '3.1.3' 
		WHEN 'Apple-iPhone1C2/801.293' THEN '4.0' 
		WHEN 'Apple-iPhone1C2/801.306' THEN '4.0.1' 
		WHEN 'Apple-iPhone1C2/801.400' THEN '4.0.2' 
		WHEN 'Apple-iPhone1C2/802.117' THEN '4.1' 
		WHEN 'Apple-iPhone1C2/803.148' THEN '4.2.1' 
		WHEN 'Apple-iPhone2C1/801.293' THEN '4.0' 
		WHEN 'Apple-iPhone2C1/801.306' THEN '4.0.1' 
		WHEN 'Apple-iPhone2C1/801.400' THEN '4.0.2' 
		WHEN 'Apple-iPhone2C1/802.117' THEN '4.1' 
		WHEN 'Apple-iPhone2C1/803.14800001' THEN '4.2.1' 
		WHEN 'Apple-iPhone2C1/807.4' THEN '4.3.1' 
		WHEN 'Apple-iPhone2C1/808.7' THEN '4.3.2' 
		WHEN 'Apple-iPhone2C1/810.2' THEN '4.3.3' 
		WHEN 'Apple-iPhone2C1/811.2' THEN '4.3.4' 
		WHEN 'Apple-iPhone2C1/812.1' THEN '4.3.5' 
		WHEN 'Apple-iPhone2C1/901.334' THEN '5.0' 
		WHEN 'Apple-iPhone2C1/901.405' THEN '5.0.1' 
		WHEN 'Apple-iPhone2C1/902.176' THEN '5.1' 
		WHEN 'Apple-iPhone2C1/902.206' THEN '5.1.1' 
		WHEN 'Apple-iPhone2C1/1001.403' THEN '6.0' 
		WHEN 'Apple-iPhone2C1/1001.523' THEN '6.0.1' 
		WHEN 'Apple-iPhone3C1/801.293' THEN '4.0' 
		WHEN 'Apple-iPhone3C1/801.306' THEN '4.0.1' 
		WHEN 'Apple-iPhone3C1/801.400' THEN '4.0.2' 
		WHEN 'Apple-iPhone3C1/802.117' THEN '4.1' 
		WHEN 'Apple-iPhone3C1/803.148' THEN '4.2.1' 
		WHEN 'Apple-iPhone3C1/807.4' THEN '4.3.1' 
		WHEN 'Apple-iPhone3C1/808.7' THEN '4.3.2' 
		WHEN 'Apple-iPhone3C1/810.2' THEN '4.3.3' 
		WHEN 'Apple-iPhone3C1/811.2' THEN '4.3.4' 
		WHEN 'Apple-iPhone3C1/812.1' THEN '4.3.5' 
		WHEN 'Apple-iPhone3C1/901.334' THEN '5.0' 
		WHEN 'Apple-iPhone3C1/901.405' THEN '5.0.1' 
		WHEN 'Apple-iPhone3C1/902.176' THEN '5.1' 
		WHEN 'Apple-iPhone3C1/902.206' THEN '5.1.1' 
		WHEN 'Apple-iPhone3C1/1001.403' THEN '6.0' 
		WHEN 'Apple-iPhone3C1/1001.523' THEN '6.0.1' 
		WHEN 'Apple-iPhone3C3/805.128' THEN '4.2.5' 
		WHEN 'Apple-iPhone3C3/805.200' THEN '4.2.6' 
		WHEN 'Apple-iPhone3C3/805.303' THEN '4.2.7' 
		WHEN 'Apple-iPhone3C3/805.401' THEN '4.2.8' 
		WHEN 'Apple-iPhone3C3/805.501' THEN '4.2.9' 
		WHEN 'Apple-iPhone3C3/805.600' THEN '4.2.10' 
		WHEN 'Apple-iPhone3C3/901.334' THEN '5.0' 
		WHEN 'Apple-iPhone3C3/901.405' THEN '5.0.1' 
		WHEN 'Apple-iPhone3C3/902.176' THEN '5.1' 
		WHEN 'Apple-iPhone3C3/902.206' THEN '5.1.1' 
		WHEN 'Apple-iPhone3C3/1001.403' THEN '6.0' 
		WHEN 'Apple-iPhone3C3/1001.523' THEN '6.0.1' 
		WHEN 'Apple-iPhone4C1/1001.403' THEN '6.0' 
		WHEN 'Apple-iPhone4C1/1001.523' THEN '6.0.1' 
		WHEN 'Apple-iPhone4C1/901.334' THEN '5.0' 
		WHEN 'Apple-iPhone4C1/901.405' THEN '5.0.1' 
		WHEN 'Apple-iPhone4C1/901.406' THEN '5.0.1' 
		WHEN 'Apple-iPhone4C1/902.179' THEN '5.1' 
		WHEN 'Apple-iPhone4C1/902.206' THEN '5.1.1' 
		WHEN 'Apple-iPhone5C1/1001.405' THEN '6.0' 
		WHEN 'Apple-iPhone5C1/1001.525' THEN '6.0.1' 
		WHEN 'Apple-iPhone5C2/1001.405' THEN '6.0' 
		WHEN 'Apple-iPhone5C2/1001.525' THEN '6.0.1' 
		/* iPad */
		WHEN 'Apple-iPad/702.367' THEN '3.2.2'
		WHEN 'Apple-iPad/702.405' THEN '3.2.1'
		WHEN 'Apple-iPad/702.500' THEN '3.2.2'
		WHEN 'Apple-iPad1C1/803.148' THEN '4.2.1'
		WHEN 'Apple-iPad1C1/806.190' THEN '4.3'
		WHEN 'Apple-iPad1C1/807.4' THEN '4.3.1'
		WHEN 'Apple-iPad1C1/808.7' THEN '4.3.2'
		WHEN 'Apple-iPad1C1/810.3' THEN '4.3.3'
		WHEN 'Apple-iPad1C1/811.2' THEN '4.3.4'
		WHEN 'Apple-iPad1C1/812.1' THEN '4.3.5'
		WHEN 'Apple-iPad1C1/901.334' THEN '5.0'
		WHEN 'Apple-iPad1C1/901.334' THEN '5.0.1'
		WHEN 'Apple-iPad1C1/901.405' THEN '5.0.1'	/* not sure */
		WHEN 'Apple-iPad1C1/902.176' THEN '5.1'
		WHEN 'Apple-iPad1C1/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad2C1/806.191' THEN '4.3'
		WHEN 'Apple-iPad2C1/807.4' THEN '4.3.1'
		WHEN 'Apple-iPad2C1/808.7' THEN '4.3.2'
		WHEN 'Apple-iPad2C1/810.2' THEN '4.3.3'
		WHEN 'Apple-iPad2C1/811.2' THEN '4.3.4'
		WHEN 'Apple-iPad2C1/812.1' THEN '4.3.5'
		WHEN 'Apple-iPad2C1/901.334' THEN '5.0'
		WHEN 'Apple-iPad2C1/901.334' THEN '5.0.1'
		WHEN 'Apple-iPad2C1/902.176' THEN '5.1'
		WHEN 'Apple-iPad2C1/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad2C1/1001.403' THEN '6.0'
		WHEN 'Apple-iPad2C1/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad2C2/806.191' THEN '4.3'
		WHEN 'Apple-iPad2C2/807.4' THEN '4.3.1'
		WHEN 'Apple-iPad2C2/808.7' THEN '4.3.2'
		WHEN 'Apple-iPad2C2/810.2' THEN '4.3.3'
		WHEN 'Apple-iPad2C2/811.2' THEN '4.3.4'
		WHEN 'Apple-iPad2C2/812.1' THEN '4.3.5'
		WHEN 'Apple-iPad2C2/901.334' THEN '5.0'
		WHEN 'Apple-iPad2C2/901.334' THEN '5.0.1'
		WHEN 'Apple-iPad2C2/901.405' THEN '5.0.1'
		WHEN 'Apple-iPad2C2/902.176' THEN '5.1'
		WHEN 'Apple-iPad2C2/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad2C2/1001.403' THEN '6.0'
		WHEN 'Apple-iPad2C2/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad2C3/806.191' THEN '4.3'
		WHEN 'Apple-iPad2C3/807.4' THEN '4.3.1'
		WHEN 'Apple-iPad2C3/808.8' THEN '4.3.2'
		WHEN 'Apple-iPad2C3/810.2' THEN '4.3.3'
		WHEN 'Apple-iPad2C3/811.2' THEN '4.3.4'
		WHEN 'Apple-iPad2C3/812.1' THEN '4.3.5'
		WHEN 'Apple-iPad2C3/901.334' THEN '5.0'
		WHEN 'Apple-iPad2C3/901.334' THEN '5.0.1'
		WHEN 'Apple-iPad2C3/902.176' THEN '5.1'
		WHEN 'Apple-iPad2C3/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad2C3/1001.403' THEN '6.0'
		WHEN 'Apple-iPad2C3/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad2C4/1001.403' THEN '6.0'
		WHEN 'Apple-iPad2C4/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad3C1/902.176' THEN '5.1'
		WHEN 'Apple-iPad3C1/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad3C1/1001.403' THEN '6.0'
		WHEN 'Apple-iPad3C1/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad3C2/902.176' THEN '5.1'
		WHEN 'Apple-iPad3C2/902.206' THEN '5.1.1'
		WHEN 'Apple-iPad3C2/1001.403' THEN '6.0'
		WHEN 'Apple-iPad3C2/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad3C3/1001.403' THEN '6.0'
		WHEN 'Apple-iPad3C3/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPad3C3/902.176' THEN '5.1'
		WHEN 'Apple-iPad3C3/902.206' THEN '5.1.1'
		/* iPOD touch */
		WHEN 'Apple-iPod/501.347' THEN '2.0'
		WHEN 'Apple-iPod/502.108' THEN '2.0.1'
		WHEN 'Apple-iPod/503.001' THEN '2.0.2'
		WHEN 'Apple-iPod/506.137' THEN '2.1'
		WHEN 'Apple-iPod/505.138' THEN '2.1.1'
		WHEN 'Apple-iPod/507.077' THEN '2.2'
		WHEN 'Apple-iPod/507.07700001' THEN '2.2'
		WHEN 'Apple-iPod/508.11' THEN '2.2.1'
		WHEN 'Apple-iPod/508.01100001' THEN '2.2.1'
		WHEN 'Apple-iPod/701.341' THEN '3.0'
		WHEN 'Apple-iPod/701.400' THEN '3.0.1'
		WHEN 'Apple-iPod/703.145' THEN '3.1'
		WHEN 'Apple-iPod/703.146' THEN '3.1'
		WHEN 'Apple-iPod/704.011' THEN '3.1.2'
		WHEN 'Apple-iPod/705.018' THEN '3.1.3'
		WHEN 'Apple-iPod2C1/801.293' THEN '4.0'
		WHEN 'Apple-iPod3C1/801.293' THEN '4.0'
		WHEN 'Apple-iPod2C1/801.306' THEN '4.0.1'
		WHEN 'Apple-iPod3C1/801.306' THEN '4.0.1'
		WHEN 'Apple-iPod2C1/801.400' THEN '4.0.2'
		WHEN 'Apple-iPod3C1/801.400' THEN '4.0.2'
		WHEN 'Apple-iPod2C1/802.117' THEN '4.1'
		WHEN 'Apple-iPod3C1/802.117' THEN '4.1'
		WHEN 'Apple-iPod4C1/802.117' THEN '4.1'
		WHEN 'Apple-iPod2C1/803.148' THEN '4.2.1'
		WHEN 'Apple-iPod3C1/803.148' THEN '4.2.1'
		WHEN 'Apple-iPod4C1/803.148' THEN '4.2.1'
		WHEN 'Apple-iPod3C1/807.4' THEN '4.3.1'
		WHEN 'Apple-iPod4C1/807.4' THEN '4.3.1'
		WHEN 'Apple-iPod3C1/808.7' THEN '4.3.2'
		WHEN 'Apple-iPod4C1/808.7' THEN '4.3.2'
		WHEN 'Apple-iPod3C1/810.2' THEN '4.3.3'
		WHEN 'Apple-iPod4C1/810.2' THEN '4.3.3'
		WHEN 'Apple-iPod3C1/811.2' THEN '4.3.4'
		WHEN 'Apple-iPod4C1/811.2' THEN '4.3.4'
		WHEN 'Apple-iPod3C1/812.1' THEN '4.3.5'
		WHEN 'Apple-iPod4C1/812.1' THEN '4.3.5'
		WHEN 'Apple-iPod3C1/901.334' THEN '5.0'
		WHEN 'Apple-iPod4C1/901.334' THEN '5.0'
		WHEN 'Apple-iPod3C1/901.334' THEN '5.0.1'
		WHEN 'Apple-iPod4C1/901.334' THEN '5.0.1'
		WHEN 'Apple-iPod3C1/902.176' THEN '5.1'
		WHEN 'Apple-iPod4C1/902.176' THEN '5.1'
		WHEN 'Apple-iPod3C1/902.206' THEN '5.1.1'
		WHEN 'Apple-iPod4C1/902.206' THEN '5.1.1'
		WHEN 'Apple-iPod4C1/1001.403' THEN '6.0'
		WHEN 'Apple-iPod5C1/1001.406' THEN '6.0'
		WHEN 'Apple-iPod4C1/1001.523' THEN '6.0.1'
		WHEN 'Apple-iPod5C1/1001.523' THEN '6.0'
	END AS Device-Version
FROM  '[LOGFILEPATH]' 
WHERE cs(user-agent) LIKE '%Apple-%'
GROUP BY User, Device,Device-Version
ORDER BY User ASC</QueryData>
    <QueryID>78aa9192-2012-4d6a-a882-90a8b11d9342</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Budget Report [100% Exceeded]</QueryName>
    <QueryDescription>Returns all ActiveSync Requests where any budget exceeds 100%</QueryDescription>
    <QueryData>/* 
    Returns all Microsoft-Server-ActiveSync requests where any budget is over 100%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
    These users are exceeding their budget.
*/

SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' 
AND (TO_INT(AD%) &gt; 100 OR TO_INT(CAS%) &gt; 100 OR TO_INT(AB%) &gt; 100 OR TO_INT(RPC%) &gt; 100 OR TO_INT(FC%) &gt; 100)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>67387ee8-a051-4010-81ef-59685157e8a5</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:19:56.0297439-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Budget Report [75% Exceeded]</QueryName>
    <QueryDescription>Returns all ActiveSync requests where any budget exceeds 75%</QueryDescription>
    <QueryData>/* 
    Returns all ActiveSync requests where any budget is over 75%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
*/
 
SELECT cs-username AS UserID,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'AD:'), 0, ',') As ADBudget,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'CAS:'), 0, ',') As CASBudget,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'AB:'), 0, ',') As ABBudget,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'RPC:'), 0, ',') As RPCBudget,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'FC:'), 0, ',') As FCBudget,
 
SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , SUB(SUB(STRLEN(ADBudget),LAST_INDEX_OF(ADBudget, '/')),2)) AS AD%,
SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1), SUB(SUB(STRLEN(CASBudget),LAST_INDEX_OF(CASBudget, '/')),2)) AS CAS%,
SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , SUB(SUB(STRLEN(ABBudget),LAST_INDEX_OF(ABBudget, '/')),2)) AS AB%,
SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , SUB(SUB(STRLEN(RPCBudget),LAST_INDEX_OF(RPCBudget, '/')),2)) AS RPC%,
SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , SUB(SUB(STRLEN(FCBudget),LAST_INDEX_OF(FCBudget, '/')),1)) AS FC%,
ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')), 0, 'Policy:'), 0, ',') As Policy,
EXTRACT_VALUE(URLUNESCAPE(EXTRACT_VALUE(cs-uri-query, 'Log')),'mbx') AS CAS,
       s-ip AS SERVER,
       cs-uri-query AS Query,
       STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
 
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
AND (TO_INT(AD%) &gt; 75 OR TO_INT(CAS%) &gt; 75 OR TO_INT(AB%) &gt; 75 OR TO_INT(RPC%) &gt; 75 OR TO_INT(FC%) &gt; 75)
 
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>ba7473ee-13d1-4e64-8440-012175be40e0</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:00.4214418-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Client Device Generated Protocol Commands</QueryName>
    <QueryDescription>Counts the number of Add/Delete/Change/Folders commands per user/device. Direction is from device to server.</QueryDescription>
    <QueryData>SELECT TOP 100
cs-username AS User,
cs(User-Agent) AS Device,
SUM(TO_INT(EXTRACT_PREFIX(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Cli:'), 0, '_'), 0, 'a'))) As Adds,
SUM(TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Cli:'), 0, '_'), 0, 'c'), 0, 'd'))) As Deletes,
SUM(TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Cli:'), 0, '_'), 0, 'd'), 0, 'f'))) As Changes,
SUM(TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Cli:'), 0, '_'), 0, 'f'), 0, 'e'))) As Folders,
Count(*) as Hits
FROM '[LOGFILEPATH]' 
WHERE cs-uri-query  LIKE '%_Cli:%' 
GROUP BY  User, Device </QueryData>
    <QueryID>cb668460-d98a-46d9-960d-6bd1d7a8d80d</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Count all Syncs per SyncKey </QueryName>
    <QueryDescription>Counts the number of Sync commands per unique SyncKey where SyncKey is not equal to zero</QueryDescription>
    <QueryData>/* Count all Syncs per SyncKey  */
 
Select Count(*) AS Total,
TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cmd='), 0, '&amp;')) As Command,
TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_')) As SyncKey
FROM '[LOGFILEPATH]'
WHERE SyncKey &gt; 0
AND cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
AND cs-uri-query LIKE '%_Sk:%'
GROUP BY SyncKey, Command
ORDER BY Total ASC</QueryData>
    <QueryID>b26d2be0-0f5b-47c9-9745-3fc98595c0d9</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Count Syncs with SyncKey of Zero Per User</QueryName>
    <QueryDescription>Counts all Sync commands per user where the SyncKey is equal to Zero</QueryDescription>
    <QueryData>/* Count all Syncs with a zero SyncKey sorted by user */
 
SELECT 
TO_LOCALTIME(TO_TIMESTAMP(TO_DATE(date), TO_TIME(time))) as [DateTime],
cs-username AS User,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_') As SyncKey,
Count(*) As Total
FROM '[LOGFILEPATH]'
WHERE SyncKey = '0'
AND cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
AND cs-uri-query LIKE '%_Sk:%'

GROUP BY SyncKey, [DateTime], User</QueryData>
    <QueryID>547864c1-1c87-4f1a-b1f9-1030b120ed83</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Devices Report [Top 20 Devices]</QueryName>
    <QueryDescription>Returns all ActiveSync hits ordered by device type and number of hits for the top 20 devices by number of hits.</QueryDescription>
    <QueryData>/* 
    Top 20 ActiveSync Devices Report by Hits. 
*/

SELECT TOP 20 cs(User-Agent) AS DeviceType, 
	Count(*) as Hits
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' 
GROUP BY DeviceType
ORDER BY Hits, DeviceType DESC</QueryData>
    <QueryID>8390f022-fb1b-47ce-b158-3ffc9068196a</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Devices Report [Top 20 Devices] Specific Device</QueryName>
    <QueryDescription>Returns all ActiveSync hits ordered by device type and number of hits for the top 20 devices by number of hits.</QueryDescription>
    <QueryData>/* 
    Top 20 ActiveSync Devices Report by Hits. Add the device string below to count a single device or device type.
*/

SELECT TOP 20 cs(User-Agent) AS DeviceType, 
	Count(*) as Hits
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs(user-Agent) LIKE /* Put device type here -&gt; */ '%iPad%'
GROUP BY DeviceType
ORDER BY Hits, DeviceType DESC</QueryData>
    <QueryID>d170f1f6-3678-443c-b8d8-c3e49f128b5d</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Errors by User to CSV</QueryName>
    <QueryDescription>Returns all ActiveSync error and aggregates them by error number and user.</QueryDescription>
    <QueryData>/* 
   THIS CREATES A FILE: EASUsersByError_500_Plus.CSV
   Change the filename/path below to suit your needs.
*/

select 
	cs-username,
	cs(User-Agent) AS DeviceType, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
 INTO '[OUTFILEPATH]\EASusersByerror_500_plus.csv'
	FROM '[LOGFILEPATH]'
	WHERE TO_STRING(sc-status) LIKE '500' AND cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-username IS NOT NULL
	Group BY cs-username, DeviceType, application, HTTPstatus
	Order BY cs-username, DeviceType</QueryData>
    <QueryID>fd2ecb66-219a-4f37-95b1-e1a875dbe261</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Exchange 2013 Active Sync Throttling Report - Count users over budget.</QueryName>
    <QueryDescription>Finds all users who have exceeded their budget and counts how many times their budget was exceeded.</QueryDescription>
    <QueryData>/*  Exchange 2013 ActiveSync Throttling Report - Overbudget counts per user */

SELECT Count(*) As Hits,
cs-username as User,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'IsServiceAccount%3a'), 0, '%2c') as [Is Service Account],
cs(user-agent) as Device
FROM '[LOGFILEPATH]' 
WHERE cs-uri-query LIKE '%Error:OverBudget%' 
GROUP BY User, Device, [Is Service Account]
ORDER BY Hits, User, Device DESC

/* Duplicate user entries means the user is connecting with more that one device version */</QueryData>
    <QueryID>766d3288-c640-44b9-bc57-2b945aa98e34</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Exchange 2013 ActiveSync Throttling Report</QueryName>
    <QueryDescription>Creates report using Micro-throttling indicators</QueryDescription>
    <QueryData>/*  Exchange 2013 ActiveSync Throttling Report  */

SELECT TOP 100
cs-username as User,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Conn%3a'), 0, '%2c') as Conn,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxConn%3a'), 0, '%2c') as [Max Conn],  
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cutoff%3a'), 0, '%2c') as Cutoff,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RechargeRate%3a'), 0, '%2c') as [Recharge Rate],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxBurst%3a'), 0, '%2c') as [Max Burst],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Balance%3a'), 0, '%2c') as Balance ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy%3a'), 0, '%2c') as Policy ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'IsServiceAccount%3a'), 0, '%2c') as IsServiceAccount 
FROM  '[LOGFILEPATH]' 
WHERE cs-uri-query LIKE '%%2cBalance%3a%' 

</QueryData>
    <QueryID>d96977ed-9f16-41dd-9855-f568d35880d0</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Exchange 2013 ActiveSync Throttling Report - Negative Balance</QueryName>
    <QueryDescription>Creates report using Micro-throttling indicators only listing users with a negative balance.</QueryDescription>
    <QueryData>/*  Exchange 2013 ActiveSync Throttling Report - Only list users with a negative balance.  */

SELECT TOP 100 
cs-username as User,
cs(user-agent) as Device,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Conn%3a'), 0, '%2c') as Conn,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxConn%3a'), 0, '%2c') as [Max Conn],  
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cutoff%3a'), 0, '%2c') as Cutoff,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RechargeRate%3a'), 0, '%2c') as [Recharge Rate],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxBurst%3a'), 0, '%2c') as [Max Burst],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Balance%3a'), 0, '%2c') as Balance ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy%3a'), 0, '%2c') as Policy ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'IsServiceAccount%3a'), 0, '%2c') as IsServiceAccount 
FROM  '[LOGFILEPATH]' 
WHERE cs-uri-query LIKE '%%2cBalance%3a%' 
AND TO_REAL(Balance) &lt; 0.0

</QueryData>
    <QueryID>0ff6a7b7-2870-4c3e-ac7a-a0363c9fe792</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-06T14:21:31.1001613-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Exchange 2013 ActiveSync User Performance Report</QueryName>
    <QueryDescription> Exchange 2013 ActiveSync User Performance Report</QueryDescription>
    <QueryData>/*  Exchange 2013 ActiveSync User Performance Report  */

SELECT
cs-username as UserName,
cs-uri-query as Request,
EXTRACT_VALUE(cs-uri-query,'Cmd') AS Cmd,
EXTRACT_VALUE(cs-uri-query,'User') AS User,
EXTRACT_VALUE(cs-uri-query,'DeviceId') AS DeviceId,
EXTRACT_VALUE(cs-uri-query,'DeviceType') AS DeviceType,
time-taken,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'BudgUse.T'), STRLEN(cs-uri-query)), 0, 'BudgUse.T%5b%5d%3d'), 0, '%3b')  as BudgetUsed,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmSt'), 16),4,12) as Start,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Balance%3a'), 0, '%2c') as Balance,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.T%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.T%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.T%5b'), 0, '%3b'), '%3b')), 3) as MapiTotalLatency,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.C%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.C%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MAPI.C%5b'), 0, '%3b'), '%3b')), 3) as MapiTotalCount,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ROP.C%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ROP.C%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ROP.C%5b'), 0, '%3b'), '%3b')), 3) as RopCount,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.T%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.T%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.T%5b'), 0, '%3b'), '%3b')), 3) as RpcTotalLatency,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.C%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.C%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC.C%5b'), 0, '%3b'), '%3b')), 3) as RpcCount,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MBLB.T%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MBLB.T%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MBLB.T%5b'), 0, '%3b'), '%3b')), 3) as MbxLogBytes,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.C%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.C%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.C%5b'), 0, '%3b'), '%3b')), 3) as MbxCallCount,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.AL%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.AL%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MB.AL%5b'), 0, '%3b'), '%3b')), 3) as MbxAvgLatency,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'STCPU.T%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'STCPU.T%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'STCPU.T%5b'), 0, '%3b'), '%3b')), 3) as StoreCpu,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ST.T%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ST.T%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ST.T%5b'), 0, '%3b'), '%3b')), 3) as StoreOpTime,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.C%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.C%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.C%5b'), 0, '%3b'), '%3b')), 3) as AdSearchCount,
SUBSTR(SUBSTR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.AL%5b'), 0, '%3b'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.AL%5b'), 0, '%3a'), '%3d'), INDEX_OF(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'ADS.AL%5b'), 0, '%3b'), '%3b')), 3) as AdSearchAvgLatency
INTO '[OUTFILEPATH]\E15-Test.csv'
FROM  '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ActiveSync%' and cs-username LIKE '%mww_WestonLegal%' AND cs-uri-query LIKE '%MAPI.T%'






</QueryData>
    <QueryID>f0490cc5-d866-46a6-9380-e5d7b99b8fa1</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Find All Non-Zero Sync Keys Per User</QueryName>
    <QueryDescription>Finds all the SyncKeys per user including additonal IIS fields.</QueryDescription>
    <QueryData>/* Find all non-zero SyncKeys per user (expanded info) */
 
Select TOP 5000 TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS [Time],
cs-username AS [User],
cs(user-agent) as Device,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'DeviceId='), 0, '&amp;') AS DeviceID,
TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_')) AS SyncKey,
cs-method AS Method,
sc-status AS [HTTP Status],
sc-substatus AS [Sub Status],
time-taken, 
DIV(time-taken, 1000) AS Sec, 
URLUNESCAPE(cs-uri-query) as Querystring
FROM '[LOGFILEPATH]'
WHERE SyncKey &gt; 0
AND cs-uri-query LIKE '%_Sk:%'
ORDER BY User</QueryData>
    <QueryID>7eca583d-8fb8-4dd2-bf0c-65e6615ccb9b</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Find All SyncKeys Per User Sorted By User</QueryName>
    <QueryDescription>ActiveSync: Find All SyncKeys Per User and sort them by user name.</QueryDescription>
    <QueryData>/* Find all SyncKeys per user sorted by user  */
 
Select TOP 5000 TO_LOCALTIME(TO_TIMESTAMP(TO_DATE(date), TO_TIME(time))) AS [DateTime],
cs-username AS [User],
cs(user-agent) as Device,
Count(cs-username) AS Total,
TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'DeviceId='), 0, '&amp;')) AS DeviceID,
TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cmd='), 0, '&amp;')) As Command,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_') AS SyncKey
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
AND cs-uri-query LIKE '%_Sk:%'
GROUP BY [DateTime], [User], Device, DeviceID, SyncKey, Command
ORDER BY [User], [DateTime] ASC</QueryData>
    <QueryID>5394952a-4b76-45e6-8a5f-50f9889e2ce3</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Find All SyncKeys Per User/Chronological Order</QueryName>
    <QueryDescription>Find all SyncKeys per user sorted chronologically </QueryDescription>
    <QueryData>/* Find all SyncKeys per user sorted chronologically */
 
Select TOP 5000 TO_LOCALTIME(TO_TIMESTAMP(TO_DATE(date), TO_TIME(time))) AS [DateTime],
cs-username AS [User],
cs(user-agent) as Device,
Count(cs-username) AS Total,
TO_STRING(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'DeviceId='), 0, '&amp;')) AS DeviceID,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_') AS SyncKey
 
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'
AND cs-uri-query LIKE '%_Sk:%'
GROUP BY [DateTime], [User], Device, DeviceID, SyncKey
ORDER BY [DateTime], [User] ASC</QueryData>
    <QueryID>5070c86c-963a-499d-aec7-6ea16a3e96f6</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Find All Zero SyncKeys Per user (Expanded)</QueryName>
    <QueryDescription>Finds all sync commands with a SyncKey of zero. This query provides additional IIS fields in the results.</QueryDescription>
    <QueryData>/* Find all zero SyncKeys (expanded info) */
 
Select TOP 5000 TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS [Time],
cs-username AS [User],
cs(user-agent) as Device,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'DeviceId='), 0, '&amp;') AS DeviceID,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '_Sk:'), 0, '_') AS SyncKey,
cs-method AS Method,
sc-status AS [HTTP Status],
sc-substatus AS [Sub Status],
time-taken, 
DIV(time-taken, 1000) AS Sec, 
URLUNESCAPE(cs-uri-query) as Querystring
FROM '[LOGFILEPATH]'
WHERE SyncKey = '0'
AND cs-uri-query LIKE '%_Sk:%'</QueryData>
    <QueryID>bb17ea84-25e3-4971-8341-340b11aa5ba3</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: High CPU caused by Mobile Devices</QueryName>
    <QueryDescription>In this scenario, the Exchange Server CAS server may run very slowly and not respond to a Microsoft Exchange ActiveSync request. When this issue occurs, the W3wp.exe process that is running under the MSExchangeSyncAppPool may use 100% of the CPU time.

REF: http://support.microsoft.com/kb/2711053</QueryDescription>
    <QueryData>/* Find Device/User Info for 100% CPU while syncing contacts */

Select TOP 5000 TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) as [Time],
cs-username as [User],
cs(user-agent) as DeviceID,
TO_INT(EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, '_RpcC'), 0, '_')) As RPCCount,
sc-status as Status,
sc-substatus as SubStatus,
sc-bytes as [Bytes],
DIV(sc-bytes, 1024) AS [KBytes], time-taken, DIV(time-taken, 1000) as Sec, cs-uri-query
FROM '[LOGFILEPATH]'
WHERE 
RPCCount &gt; 2000 /* &lt;-- Change RPC count as needed */
AND cs-uri-query LIKE '%Cmd=Sync%' 
AND cs-uri-query LIKE '%Ty:Co%'
ORDER BY [Bytes] DESC

/* REF: http://support.microsoft.com/kb/2711053 */</QueryData>
    <QueryID>50350c28-aedc-4471-9579-3c159a76bdac</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:29.2622618-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: HTTP 500 /Hour</QueryName>
    <QueryDescription>EAS requests per hour with Error 500 description. You can search for specific full error codes such as 500.0.0 etc.</QueryDescription>
    <QueryData>/* You will want to enter the http error you want below if you wish to narrow down. IE: 401.1.1326 */

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS halfhour,  /*3600 = hour, 1800=halfhour, 180= 3min*/
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir,
	STRCAT(STRCAT(STRCAT(TO_STRING(sc-status),'.'),STRCAT(TO_STRING(sc-substatus),'.')),TO_STRING(sc-win32-status)) as HTTPstatus,
	cs-uri-stem,
	cs-uri-query,
	cs-username,
	cs(Cookie)
INTO '[OUTFILEPATH]\EAShitsperhourwitherrorBadLogon_plus.csv'
FROM '[LOGFILEPATH]'

WHERE cs-uri-stem LIKE '%Microsoft-Server-Activesync%' AND HTTPstatus LIKE '%50%' /*change the error here as needed */
GROUP BY halfhour, Vdir, HTTPstatus, cs-uri-stem, cs-uri-query, cs-username, cs(Cookie)
ORDER BY halfhour
</QueryData>
    <QueryID>5529b3d3-9548-4000-800e-a43dc8dbe7ed</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: HTTP 503 1/2 hour</QueryName>
    <QueryDescription>EAS requests per hour with Error 503 description</QueryDescription>
    <QueryData>SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 900) AS halfhour,  /*3600 = hour, 1800=halfhour, 180= 3min*/
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir,
	STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-Activesync%' AND HTTPstatus = '503.0' /*change the error here as needed */
GROUP BY halfhour, Vdir, HTTPstatus
ORDER BY halfhour
</QueryData>
    <QueryID>68bb229e-db6d-4724-881c-fa892c7ec4b7</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: iPhone Report by User/Hits/Device</QueryName>
    <QueryDescription>iPhone report by IP/User/iPhoneVersion/Hits</QueryDescription>
    <QueryData>/* iPhone report by IP/User/iPhoneVersion/Hits */
    
SELECT c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits 
from '[LOGFILEPATH]'
WHERE cs(User-Agent) LIKE '%iPhone%' AND cs-username IS NOT NULL 
GROUP BY User, c-ip, cs(User-Agent) 
ORDER BY ExchangeHits DESC
</QueryData>
    <QueryID>9f937d5a-9e90-4c50-b1be-2eafa4edfe21</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: iPhone Top 20 Report by User/Hits/Device</QueryName>
    <QueryDescription>iPhone report by IP/User/iPhoneVersion/Hits</QueryDescription>
    <QueryData>/* iPhone report by IP/User/iPhoneVersion/Hits */
    
SELECT TOP 20 c-ip AS ClientIP, cs-username AS User, cs(User-Agent) AS Client, Count(cs-username) AS ExchangeHits 
from '[LOGFILEPATH]'
WHERE cs(User-Agent) LIKE '%iPhone%' AND cs-username IS NOT NULL 
GROUP BY User, c-ip, cs(User-Agent) 
ORDER BY ExchangeHits DESC
</QueryData>
    <QueryID>18a79b8d-c858-4d38-9ec7-1afe50e5bb34</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Requests &gt; 8 Seconds</QueryName>
    <QueryDescription>EAS 8 &gt; 8000ms time-taken</QueryDescription>
    <QueryData>SELECT 
		date,
		time,
		s-ip,
		cs-method,
		cs-uri-query,
		cs(user-agent),
		sc-status,
		sc-substatus,
		sc-win32-status,
		time-taken
INTO '[OUTFILEPATH]\EAS8sec.csv'
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%'  AND cs-uri-query NOT LIKE '%Cmd=Ping%' AND time-taken&gt;8000
GROUP BY date, time, s-ip, cs-method, cs-uri-query, cs(user-Agent), sc-status, sc-substatus, sc-win32-status, time-taken
ORDER BY time-taken DESC
</QueryData>
    <QueryID>bbeb382a-553a-439c-b481-ba10120e407c</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Status Code Report</QueryName>
    <QueryDescription>HTTP status codes counted by user including friendly error descriptions for status and, sub-status and win32 error codes.</QueryDescription>
    <QueryData>/* ActiveSync Status/Error report by user and status code count */
/* Includes friendly names for any errors                       */

SELECT
    cs-username, cs-uri-stem, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
    CASE HTTPstatus
        when'200.0' then'HttpStatusOk'                  
        when'201.0' then'HttpStatusCreated'             
        when'204.0' then'HttpStatusNoContent' 
        when'206.0' then'HttpStatusPartialContent' 
        when'207.0' then'HttpStatusMultiStatus' 
        when'301.0' then'HttpStatusMovedPermanently' 
        when'302.0' then'HttpStatusRedirect' 
        when'307.0' then'HttpStatusMovedTemporarily' 
        when'304.0' then'HttpStatusNotModified' 
        when'400.0' then'HttpStatusBadRequest' 
        when'400.1' then'HttpStatusInvalidDestination' 
        when'400.2' then'HttpStatusInvalidDepth' 
        when'400.3' then'HttpStatusInvalidIf' 
        when'400.4' then'HttpStatusInvalidOverwrite' 
        when'400.5' then'HttpStatusInvalidTranslate' 
        when'400.6' then'HttpStatusInvalidRequestBody' 
        when'400.7' then'HttpStatusInvalidContentLength' 
        when'401.0' then'HttpStatusUnauthorized' 
        when'401.1' then'HttpStatusBadLogon' 
        when'401.2' then'HttpStatusDeniedConfig' 
        when'401.3' then'HttpStatusDeniedResource' 
        when'401.4' then'HttpStatusDeniedFilter' 
        when'401.5' then'HttpStatusDeniedApplication' 
        when'403.0' then'HttpStatusForbidden' 
        when'403.1' then'HttpStatusExecAccessDenied' 
        when'403.2' then'HttpStatusReadAccessDenied' 
        when'403.3' then'HttpStatusWriteAccessDenied' 
        when'403.4' then'HttpStatusSSLRequired' 
        when'403.5' then'HttpStatusSSL128Required' 
        when'403.6' then'HttpStatusIPAddressReject' 
        when'403.7' then'HttpStatusCertRequired' 
        when'403.8' then'HttpStatusSiteAccessDenied' 
        when'403.9' then'HttpStatusTooManyUsers' 
        when'403.11' then'HttpStatusPasswordChange' 
        when'403.12' then'HttpStatusMapperDenyAccess' 
        when'403.13' then'HttpStatusCertRevoked' 
        when'403.14' then'HttpStatusDirBrowsingDenied' 
        when'403.16' then'HttpStatusCertInvalid' 
        when'403.17' then'HttpStatusCertTimeInvalid' 
        when'403.18' then'HttpStatusAppPoolDenied' 
        when'403.19' then'HttpStatusInsufficientPrivilegeForCgi' 
        when'403.20' then'HttpStatusPassportLoginFailure' 
        when'403.21' then'HttpStatusSourceAccessDenied' 
        when'403.22' then'HttpStatusInfiniteDepthDenied' 
        when'404.0' then'HttpStatusNotFound' 
        when'404.2' then'HttpStatusDeniedByPolicy' 
        when'404.3' then'HttpStatusDeniedByMimeMap' 
        when'404.4' then'HttpStatusNoHandler' 
        when'404.5' then'HttpStatusDeniedByUrlSequence' 
        when'404.6' then'HttpStatusDeniedByVerb' 
        when'404.7' then'HttpStatusDeniedByFileExtension' 
        when'404.8' then'HttpStatusHiddenSegment' 
        when'404.9' then'HttpStatusFileAttributeHidden' 
        when'404.10' then'HttpStatusRequestHeaderTooLong' 
        when'404.11' then'HttpStatusUrlDoubleEscaped' 
        when'404.12' then'HttpStatusUrlHasHighBitChars' 
        when'404.13' then'HttpStatusContentLengthTooLarge' 
        when'404.14' then'HttpStatus404UrlTooLong' 
        when'404.15' then'HttpStatusQueryStringTooLong' 
        when'404.16' then'HttpStatusStaticFileDav' 
        when'404.17' then'HttpStatusPreconditionedHandler' 
        when'404.18' then'HttpStatusDeniedByQueryStringSequence' 
        when'404.19' then'HttpStatusDeniedByFilteringRule' 
        when'405.0' then'HttpStatusMethodNotAllowed' 
        when'406.0' then'HttpStatusNotAcceptable' 
        when'407.0' then'HttpStatusProxyAuthRequired' 
        when'409.0' then'HttpStatusConflict' 
        when'412.0' then'HttpStatusPreconditionFailed' 
        when'413.0' then'HttpStatusEntityTooLarge' 
        when'414.0' then'HttpStatusUrlTooLong' 
        when'415.0' then'HttpStatusUnsupportedMediaType' 
        when'416.0' then'HttpStatusRangeNotSatisfiable' 
        when'417.0' then'HttpStatusExpectationFailed' 
        when'423.0' then'HttpStatusLockedError' 
        when'424.0' then'HttpStatusFailedDependency' 
        	when'449.0' then'ActiveSync device blocked due to policy: retry?'
        when'500.0' then'HttpStatusServerError' 
        when'500.16' then'HttpStatusUNCAccess' 
        when'500.19' then'HttpStatusBadMetadata' 
        when'500.21' then'HttpStatusHandlersModule' 
        when'500.22' then'HttpStatusAspnetModules' 
        when'500.23' then'HttpStatusAspnetHandlers' 
        when'500.24' then'HttpStatusAspnetImpersonation' 
        when'501.0' then'HttpStatusNotImplemented' 
        when'502.0' then'HttpStatusBadGateway' 
        when'502.1' then'HttpStatusTimeout' 
        when'502.2' then'HttpStatusPrematureExit' 
        when'502.3' then'HttpStatusForwarderConnectionError' 
        when'503.0' then'HttpStatusServiceUnavailable' 
        when'503.2' then'HttpStatusAppConcurrent' 
        when'504.0' then'HttpStatusGatewayTimeout' 
        when'507.0' then'HttpStatusInsufficientStorage' 
    END as w3-status-details, 
    CASE sc-win32-status 
        WHEN 0 then'' 
        WHEN 2148074254 then 'handshake in progress' 
        ELSE WIN32_ERROR_DESCRIPTION(sc-win32-status) 
    END as win32-error, 
    sc-win32-status,
	Count(*)
 /* to File--&gt; c:\users\documents\MAS_StatusCode_Report.csv */
	FROM '[LOGFILEPATH]'
	WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-username IS NOT NULL
	Group BY cs-username, cs-uri-stem, HTTPstatus, w3-status-details, win32-error, sc-win32-status
	Order BY cs-username, cs-uri-stem</QueryData>
    <QueryID>9fd6b41d-8733-4917-977d-31f6b181e615</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Status Code Report II</QueryName>
    <QueryDescription>ActiveSync HTTP Status codes including text description of the status codes</QueryDescription>
    <QueryData>select 
    cs-uri-stem,
	cs-method,
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
    CASE HTTPstatus
        when'200.0' then'HttpStatusOk'                  
        when'201.0' then'HttpStatusCreated'             
        when'204.0' then'HttpStatusNoContent' 
        when'206.0' then'HttpStatusPartialContent' 
        when'207.0' then'HttpStatusMultiStatus' 
        when'301.0' then'HttpStatusMovedPermanently' 
        when'302.0' then'HttpStatusRedirect' 
        when'307.0' then'HttpStatusMovedTemporarily' 
        when'304.0' then'HttpStatusNotModified' 
        when'400.0' then'HttpStatusBadRequest' 
        when'400.1' then'HttpStatusInvalidDestination' 
        when'400.2' then'HttpStatusInvalidDepth' 
        when'400.3' then'HttpStatusInvalidIf' 
        when'400.4' then'HttpStatusInvalidOverwrite' 
        when'400.5' then'HttpStatusInvalidTranslate' 
        when'400.6' then'HttpStatusInvalidRequestBody' 
        when'400.7' then'HttpStatusInvalidContentLength' 
        when'401.0' then'HttpStatusUnauthorized' 
        when'401.1' then'HttpStatusBadLogon' 
        when'401.2' then'HttpStatusDeniedConfig' 
        when'401.3' then'HttpStatusDeniedResource' 
        when'401.4' then'HttpStatusDeniedFilter' 
        when'401.5' then'HttpStatusDeniedApplication' 
        when'403.0' then'HttpStatusForbidden' 
        when'403.1' then'HttpStatusExecAccessDenied' 
        when'403.2' then'HttpStatusReadAccessDenied' 
        when'403.3' then'HttpStatusWriteAccessDenied' 
        when'403.4' then'HttpStatusSSLRequired' 
        when'403.5' then'HttpStatusSSL128Required' 
        when'403.6' then'HttpStatusIPAddressReject' 
        when'403.7' then'HttpStatusCertRequired' 
        when'403.8' then'HttpStatusSiteAccessDenied' 
        when'403.9' then'HttpStatusTooManyUsers' 
        when'403.11' then'HttpStatusPasswordChange' 
        when'403.12' then'HttpStatusMapperDenyAccess' 
        when'403.13' then'HttpStatusCertRevoked' 
        when'403.14' then'HttpStatusDirBrowsingDenied' 
        when'403.16' then'HttpStatusCertInvalid' 
        when'403.17' then'HttpStatusCertTimeInvalid' 
        when'403.18' then'HttpStatusAppPoolDenied' 
        when'403.19' then'HttpStatusInsufficientPrivilegeForCgi' 
        when'403.20' then'HttpStatusPassportLoginFailure' 
        when'403.21' then'HttpStatusSourceAccessDenied' 
        when'403.22' then'HttpStatusInfiniteDepthDenied' 
        when'404.0' then'HttpStatusNotFound' 
        when'404.2' then'HttpStatusDeniedByPolicy' 
        when'404.3' then'HttpStatusDeniedByMimeMap' 
        when'404.4' then'HttpStatusNoHandler' 
        when'404.5' then'HttpStatusDeniedByUrlSequence' 
        when'404.6' then'HttpStatusDeniedByVerb' 
        when'404.7' then'HttpStatusDeniedByFileExtension' 
        when'404.8' then'HttpStatusHiddenSegment' 
        when'404.9' then'HttpStatusFileAttributeHidden' 
        when'404.10' then'HttpStatusRequestHeaderTooLong' 
        when'404.11' then'HttpStatusUrlDoubleEscaped' 
        when'404.12' then'HttpStatusUrlHasHighBitChars' 
        when'404.13' then'HttpStatusContentLengthTooLarge' 
        when'404.14' then'HttpStatus404UrlTooLong' 
        when'404.15' then'HttpStatusQueryStringTooLong' 
        when'404.16' then'HttpStatusStaticFileDav' 
        when'404.17' then'HttpStatusPreconditionedHandler' 
        when'404.18' then'HttpStatusDeniedByQueryStringSequence' 
        when'404.19' then'HttpStatusDeniedByFilteringRule' 
        when'405.0' then'HttpStatusMethodNotAllowed' 
        when'406.0' then'HttpStatusNotAcceptable' 
        when'407.0' then'HttpStatusProxyAuthRequired' 
        when'409.0' then'HttpStatusConflict' 
        when'412.0' then'HttpStatusPreconditionFailed' 
        when'413.0' then'HttpStatusEntityTooLarge' 
        when'414.0' then'HttpStatusUrlTooLong' 
        when'415.0' then'HttpStatusUnsupportedMediaType' 
        when'416.0' then'HttpStatusRangeNotSatisfiable' 
        when'417.0' then'HttpStatusExpectationFailed' 
        when'423.0' then'HttpStatusLockedError' 
        when'424.0' then'HttpStatusFailedDependency' 
	when'449.0' then'ActiveSync device blocked due to policy'
        when'500.0' then'HttpStatusServerError' 
        when'500.16' then'HttpStatusUNCAccess' 
        when'500.19' then'HttpStatusBadMetadata' 
        when'500.21' then'HttpStatusHandlersModule' 
        when'500.22' then'HttpStatusAspnetModules' 
        when'500.23' then'HttpStatusAspnetHandlers' 
        when'500.24' then'HttpStatusAspnetImpersonation' 
        when'501.0' then'HttpStatusNotImplemented' 
        when'502.0' then'HttpStatusBadGateway' 
        when'502.1' then'HttpStatusTimeout' 
        when'502.2' then'HttpStatusPrematureExit' 
        when'502.3' then'HttpStatusForwarderConnectionError' 
        when'503.0' then'HttpStatusServiceUnavailable' 
        when'503.2' then'HttpStatusAppConcurrent' 
        when'504.0' then'HttpStatusGatewayTimeout' 
        when'507.0' then'HttpStatusInsufficientStorage' 
    END as w3-status-details, 
    CASE sc-win32-status 
        WHEN 0 then'' 
        WHEN 2148074254 then 'handshake in progress' 
        ELSE WIN32_ERROR_DESCRIPTION(sc-win32-status) 
    END as win32-error, 
    sc-win32-status
	FROM '[LOGFILEPATH]'
	WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-method LIKE 'OPTIONS'
	Group BY cs-uri-stem, cs-method, HTTPstatus, w3-status-details, win32-error, sc-win32-status
	Order BY cs-uri-stem</QueryData>
    <QueryID>11ffcdac-dacf-4228-9a3d-effdc8d6de42</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-05T23:35:25.3186007-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Top 100 Users per Device</QueryName>
    <QueryDescription>Returns all ActiveSync hits ordered by device type and number of hits for the top 20 devices by number of hits.</QueryDescription>
    <QueryData>/* 
    Top 100 ActiveSync users by device. If a user has multiple devices they will show up in this report.
*/

SELECT TOP 100 cs-username as UserName, 
	Count(*) as Hits,
 cs(user-agent) as DeviceType,
 cs-uri-stem as VDir
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' 
GROUP BY DeviceType, cs-username, VDir
ORDER BY UserName, Hits DESC</QueryData>
    <QueryID>54d851fa-66a4-407f-80d2-42266d52bdad</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Top 3 ActiveSync Users</QueryName>
    <QueryDescription>Finds the top 3 ActiveSync users and lists them</QueryDescription>
    <QueryData>SELECT TOP 3 cs-username AS UserID, 
	cs(User-Agent) AS DeviceType, 
	cs-uri-stem AS Vdir,
	c-ip AS CLIENT,
	cs-method,
	Count(*)
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-method LIKE 'OPTIONS'
GROUP BY UserID, DeviceType, Vdir, Client, cs-method
ORDER BY COUNT(*) DESC</QueryData>
    <QueryID>8e728a18-42b2-4a07-9aa2-91b4633e602b</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: Unique Devices by # Hits</QueryName>
    <QueryDescription>Returns all ActiveSync hits ordered by unique device type. This query will return a list of all the mobile devices that are syncing with Exchange</QueryDescription>
    <QueryData>/* 
    List of all device types using ActiveSync and the total hits per device
*/

SELECT DISTINCT 
 cs(user-agent) as DeviceType,
 COUNT(*) as Hits
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' 
GROUP BY DeviceType
ORDER BY Hits DESC</QueryData>
    <QueryID>0c5d4c2c-f8a0-4c5b-9214-ba9e2f51259a</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: User Bandwidth &gt; 10MB</QueryName>
    <QueryDescription>Finds all users whose total bandwidth is greater than 10MB for a single request. This query requires the IIS extended fields cs-bytes and sc-bytes which are not enabled in IIS by default.</QueryDescription>
    <QueryData>/*  Find all ActiveSync users with total bandwidth exceeding 10MB */
/*  Requires cs-bytes field presence in the IIS log               */

SELECT cs(User-Agent), Div(DIV(sc-bytes, 1024),1024) as MegaBytes, sc-bytes as Bytes, cs-username As UserName 
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%/Microsoft-Server-ActiveSync%' 
AND cs-username &lt;&gt; NULL 
AND ADD(sc-bytes, cs-bytes) &gt; MUL(10000, 1024)
ORDER BY MegaBytes DESC</QueryData>
    <QueryID>288245dc-255c-4a6b-9cf4-a2766ed5909c</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: User Bandwidth Top 100</QueryName>
    <QueryDescription>Find top 100 users by total bandwidth for a single request. This query requires the IIS extended fields cs-bytes and sc-bytes which are not enabled in IIS by default.</QueryDescription>
    <QueryData>/*  Find top 100 ActiveSync users by total bandwidth */
/*  Requires cs-bytes field presence in the IIS log               */

SELECT TOP 100 cs(User-Agent) as Device, 
ADD(DIV(DIV(sc-bytes, 1024),1024), DIV(DIV(cs-bytes, 1024),1024)) as MegaBytes,
ADD(DIV(sc-bytes, 1024),DIV(cs-bytes, 1024)) as KiloBytes,  
ADD(sc-bytes, cs-bytes) as Bytes, 
cs-username As UserName 
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%/Microsoft-Server-ActiveSync%' 
AND cs-username &lt;&gt; NULL 
ORDER BY Bytes, KiloBytes, MegaBytes DESC</QueryData>
    <QueryID>77af3d8f-aa81-44c4-a038-caf132f214d1</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ActiveSync: User by Proxy Error</QueryName>
    <QueryDescription>Find users on the target CAS with proxy errors</QueryDescription>
    <QueryData>--&gt; This script to be run against IIS logs on proxied-to CAS or the non Internet facing CAS
--&gt; It picks out the user from the cs-uri-query which only exists in proxy requests

select 
	EXTRACT_Value(cs-uri-query,'User') AS Username,
	cs(User-Agent) AS DeviceType, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
	FROM '[LOGFILEPATH]'
	WHERE HTTPstatus BETWEEN '402' AND '599' AND cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND Username IS NOT NULL
	Group BY username, DeviceType, application, HTTPstatus
	Order BY username, DeviceType</QueryData>
    <QueryID>7a7704fe-6abd-4883-b4b2-69f83ec0c194</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Authentication Failures</QueryName>
    <QueryDescription>Count IIS authentication errors per user</QueryDescription>
    <QueryData>SELECT 	cs-username, 
	sc-status, 
	COUNT(*) AS Total 
FROM '[LOGFILEPATH]' 
WHERE cs-username IS NOT NULL AND sc-status BETWEEN 401 AND 403
GROUP BY cs-username,sc-status, cs-uri-stem 
ORDER BY Total DESC
</QueryData>
    <QueryID>59b74fbf-e103-46b4-922d-0a0cc0e14911</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Autodiscover Proxy: Exchange 2013 requests per hour per user</QueryName>
    <QueryDescription>Requests per hour per user, user-agent and http status.

Exchange 2013 Only</QueryDescription>
    <QueryData>/*  Exchange 2013 - Autodiscover Proxy: Requests per hour per user, user-agent and http status  */

SELECT TOP 10000  
Count (*) as Total, TO_STRING(QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX
(EXTRACT_SUFFIX([DateTime], 0, 'T'), 0, '.'), 'hh:mm:ss')), 3600), 'hh:mm:ss') As [Time], 
AuthenticatedUser, UserAgent, HttpStatus
FROM '[LOGFILEPATH]'
WHERE HttpStatus IS NOT NULL
GROUP BY [Time], AuthenticatedUser, UserAgent, HttpStatus

</QueryData>
    <QueryID>ea88dcba-bed2-485d-896f-49bbce50b600</QueryID>
    <LogType>EELXLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-03T20:52:40.5375915-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Autodiscover: Slow Requests by User</QueryName>
    <QueryDescription>Finds all autodiscover requests &gt; 2 seconds.</QueryDescription>
    <QueryData>/*  Autodiscover: Find all requests that take more than 2 seconds to complete */

SELECT   
 time-taken as Milliseconds, 
 cs-username, COUNT(*) AS Hits, 
 cs(user-agent), 
 sc-status
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%Autodiscover%'
AND time-taken &gt; 2000 /* &lt;- milliseconds change to taste */
Group BY  Milliseconds, sc-status, cs-username, cs-uri-query, sc-status, cs(user-agent)
Order By  Milliseconds DESC</QueryData>
    <QueryID>309bdf17-58bd-4657-b37e-77e164ea142e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Average Bandwidth per 1/2 Hour</QueryName>
    <QueryDescription>Returns the average bandwidth, as KB per second broken down into 30 minute blocks</QueryDescription>
    <QueryData>/* Returns the average bandwidth, as KBytes per second, broken into 30 minute blocks            */
/* The sc-bytes and cs-bytes fields are required for this query, these fields are not enabled by default in IIS   */

SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as HalfHour, ADD(SCKBytesSec, CSKBytesSec) as KBytesSec 
USING DIV(DIV(MUL(1.0, SUM(sc-bytes)), 1024), 1800) as SCKbytesSec, 
DIV(DIV(MUL(1.0, SUM(cs-bytes)), 1024), 1800) as CSKBytesSec 
INTO '[OUTFILEPATH]\AvgKBytesPerSec.CSV'
FROM '[LOGFILEPATH]' GROUP BY HalfHour ORDER BY HalfHour ASC</QueryData>
    <QueryID>f410f5ee-2b87-45a8-b82d-e7508e745bf5</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Avg Response for a User</QueryName>
    <QueryDescription>Average response time for a specific user</QueryDescription>
    <QueryData>/* Avg Response for a specific user */

Select cs-username AS UserName, 
AVG(time-taken) AS AvgTime,  
Count(*) AS Hits 
FROM '[LOGFILEPATH]' 
WHERE cs-username = '%REPLACE_WITH_USERNAME%'
GROUP BY cs-username</QueryData>
    <QueryID>517d8373-3972-426e-a4e2-da0e7e6f75f4</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Avg Response for all users</QueryName>
    <QueryDescription>Average response time for all users</QueryDescription>
    <QueryData>/* Avg Response for all users */

Select cs-username AS UserName, 
AVG(time-taken) AS AvgTime, 
Count(*) AS Hits 
FROM '[LOGFILEPATH]' 
GROUP BY cs-username 
Order BY AvgTime DESC</QueryData>
    <QueryID>16cddbfd-104c-47a8-a66c-94d1d3efacf4</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>CAS: All Requests with more than one hundred 500 errors.</QueryName>
    <QueryDescription>All Requests with more than one hundred 500 errors.</QueryDescription>
    <QueryData>/*  Find all Exchange requests with more than one hundred
    500 errors and perform a reverse dns lookup on the client IP*/

SELECT DISTINCT REVERSEDNS(c-ip) AS ClientIP,
cs-uri-stem AS RequestURI,
cs-uri-query AS QueryString,
COUNT(*) As Total
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%/owa%' OR cs-uri-stem LIKE '%/ews%' OR cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' OR cs-uri-stem LIKE '%/PowerShell%'
OR cs-uri-stem LIKE '%/exchange%' OR cs-uri-stem LIKE '%/ecp%' OR cs-uri-stem LIKE '%/UnifiedMessaging%' OR cs-uri-stem LIKE '%/exchweb%'
AND sc-status = 500
GROUP BY ClientIP, RequestURI, QueryString
HAVING Total &gt; 10
ORDER BY Total DESC</QueryData>
    <QueryID>ff5084ce-57a0-4628-9a91-6c5362597f6e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>CAS-CAS Proxy</QueryName>
    <QueryDescription>All requests made by another CAS server as a PROXY client. Run this on the non-internet facing CAS. IE: the proxy target</QueryDescription>
    <QueryData>/* Return requests made by other CAS servers (Proxy) on behalf of a user */
/* TODO: TEST THIS QUERY */

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) as Timestamp,
 cs-username AS SourceCAS, 
 cs(user-agent), 
 c-ip as SourceIP, 
 s-ip as TargetIP,  
 cs-uri-stem, cs-uri-query, sc-status, time-taken 
FROM '[LOGFILEPATH]'
WHERE TO_UPPERCASE(cs(user-agent))
LIKE '%PROXY%'
ORDER BY Timestamp, cs(user-agent) ASC

</QueryData>
    <QueryID>97f1d41f-249e-4350-97bd-f4c9c112d72f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ECP: by Username [CAS Proxy Target]</QueryName>
    <QueryDescription>Find users accessing /ECP counts their hits and errors. This is for a proxy targetnon-internet facing CAS only. See the other query for the Internet facing version.</QueryDescription>
    <QueryData>/* This script to be run against IIS logs on proxied-to CAS or the non Internet facing CAS
   It picks out the user from the cs-uri-query which only exists in proxy requests
   Targets users accessing the EX2010 /ECP directory
*/

select 
	cs-username as Username,
	cs(User-Agent) AS ECPClient, 
    TO_LOWERCASE(cs-uri-stem) AS ECPDirectory, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
	FROM '[LOGFILEPATH]'
	WHERE application LIKE '%ECP%' AND cs-username IS NOT NULL
	Group BY cs-username, ECPClient, ECPDirectory, HTTPstatus
	Order BY cs-username, ECPClient</QueryData>
    <QueryID>fcc3d429-f06a-4669-878b-1a82df58da08</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ECP</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ECP: Request by Username [CAS INTERNET]</QueryName>
    <QueryDescription>Find users accessing /ECP counts their hits and errors. This is for a Internet facing CAS only. See the other query for the non-Internet facing version.</QueryDescription>
    <QueryData>/* This script to be run against IIS logs on the Internet Facing CAS 
   Targets users accessing the EX2010 /ECP directory
*/

select 
	cs-username as Username,
	cs(User-Agent) AS ECPClient, 
    TO_LOWERCASE(cs-uri-stem) AS ECPDirectory, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
	FROM '[LOGFILEPATH]'
	WHERE ECPDirectory LIKE '%ECP%' AND cs-username IS NOT NULL AND cs-uri-stem NOT LIKE '%ExchangeWebServicesProxy%'
	Group BY cs-username, ECPClient, ECPDirectory, HTTPstatus
	Order BY cs-username, ECPClient</QueryData>
    <QueryID>700b08af-1c38-4c23-a95c-fce933e112d1</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ECP</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Error Report by Status</QueryName>
    <QueryDescription>Gather all errors, count them and total them.</QueryDescription>
    <QueryData>SELECT 	STRCAT(	cs-uri-stem, 
		REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
		) AS Request, 
	STRCAT(	TO_STRING(sc-status), 		
		STRCAT(	'.',
			COALESCE(TO_STRING(sc-substatus), '?' )
			)
		) AS Status, 
	COUNT(*) AS Total 
FROM '[LOGFILEPATH]'
WHERE (sc-status &gt;= 400) 
GROUP BY Request, Status 
ORDER BY Total DESC
</QueryData>
    <QueryID>41ac9b96-9d77-4b72-9d2e-d3cedeac3c43</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Count Application Errors per Hour</QueryName>
    <QueryDescription>This query counts the Errors and Warnings per hour in the local machine's Application Event log. To query a remote machine you must have admin permissions and using a path similar to the following:

\\COMPUTERNAME\Application</QueryDescription>
    <QueryData>/* Only the field names for the type of event log you are querying will be returned*/

SELECT  
QUANTIZE(TO_TIMESTAMP(TO_TIME(timegenerated), TO_DATE(TimeGenerated)), 3600) AS Hour,
Count(*) AS [Count],
EventID, EventTypeName, SourceName,
ComputerName, Message
From Application
WHERE EventTypeName NOT LIKE '%Information%' 
AND EventTypeName NOT LIKE '%Success%'
GROUP BY Hour, EventID, EventTypeName, SourceName, ComputerName, Message 
ORDER BY Hour DESC</QueryData>
    <QueryID>3ab3d05c-8e0d-46f9-a7ed-6d61ef1caa65</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T22:28:19.9504397-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Count Application Log Entries</QueryName>
    <QueryDescription>Counts number of times each event in the application log has been logged.</QueryDescription>
    <QueryData>/*  Count number of times each Event is Logged [Experimental]  */

SELECT SourceName, 
    BIT_AND(EventID, 0x3fffffff) as EventID, 
    EventTypeName, 
    COUNT(*) As Entries
    FROM SECURITY /* &lt;- logname OR \\computername\logname */
    GROUP BY SourceName, EventID, EventTypeName
ORDER BY Entries DESC

</QueryData>
    <QueryID>4eb2013e-6b20-4720-9da2-5a18c85f1498</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>EVT</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Count Errors and Warnings Every 24 hours</QueryName>
    <QueryDescription>Report counts all errors and warnings per day</QueryDescription>
    <QueryData>/*  Report counts all errors and warnings per day */

SELECT QUANTIZE(TimeGenerated, 86400) AS Day, COUNT(*) AS [Total Errors]
FROM APPLICATION
WHERE EventType = 1 OR EventType = 2
GROUP BY Day
ORDER BY Day ASC

/* Levels: 1=Error, 2=Warning */</QueryData>
    <QueryID>2bd4041f-f9b9-47f1-bf14-3887afb09122</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Find All Remote Logons</QueryName>
    <QueryDescription>Find All Remote Logons</QueryDescription>
    <QueryData>SELECT 
      timegenerated, 
      EXTRACT_TOKEN(Strings,1,'|') AS Domain, 
      RESOLVE_SID(EXTRACT_TOKEN(Strings,0,'|')) AS User, 
      EXTRACT_TOKEN(Strings,3,'|') AS SessionName,
      RESOLVE_SID(EXTRACT_TOKEN(Strings,4,'|')) AS ClientName,
      EXTRACT_TOKEN(Strings,5,'|') AS ClientAddress,
      EventID
FROM Security
WHERE EventID=4624 /* xp/2003 = 682 */
ORDER BY timegenerated </QueryData>
    <QueryID>eb6a64c4-02a7-4afd-b8a0-269906c1e3b9</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Find Event Log Field Names</QueryName>
    <QueryDescription>This query only returns the field names of the event log you wish to query.</QueryDescription>
    <QueryData>/* Only the field names for the type of event log you are querying will be returned*/

SELECT TOP 0 * From Application</QueryData>
    <QueryID>2c0e95cb-868e-413d-a9ac-6893462b5a92</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Events: Query an exported event log file.</QueryName>
    <QueryDescription>Queries and .evt or evtx event log file.</QueryDescription>
    <QueryData>/* Events: Query an exported Event log */

SELECT TOP 10 * FROM 'Samples\SampleEvents.evtx'</QueryData>
    <QueryID>ec717db3-3f8b-4947-9c02-172db5a31b8d</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T23:19:27.6701231-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Sample</QueryName>
    <QueryDescription>Sample Event log test</QueryDescription>
    <QueryData>/*  Find all Event IDs = 1000 that occur per hour */

SELECT quantize(to_time(timegenerated), 3600) AS hour,
Count(*) AS Number_of_Events
FROM Application
WHERE EventID=1000
GROUP BY Hour
ORDER BY Number_of_Events</QueryData>
    <QueryID>88b76198-13b9-4fde-98f4-09cd00fd8cbe</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EVENTS: Top 1000 Errors and Warnings</QueryName>
    <QueryDescription>Find the top 1000 errors and warnings from newest to oldest</QueryDescription>
    <QueryData>/*  Find top 1000 warnings and errors in the Application Log 
    Levels: 1=Error, 2=Warning                                 */

SELECT TOP 1000 
TimeGenerated, 
BIT_AND(EventID, 0x3fffffff) as EventID,
EventTypeName as Name,
SourceName as Source,
Strings, Message, Data 
INTO '[OUTFILEPATH]\ApplicationLogErrorsWarningTop1000.CSV'
FROM APPLICATION
WHERE EventType = 1 OR EventType = 2
ORDER BY TimeGenerated DESC


</QueryData>
    <QueryID>d0bb3825-8592-484e-8d22-57ce1691a332</QueryID>
    <LogType>EVTLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: [500 Errors]</QueryName>
    <QueryDescription>Finds EWS HTTP 500 errors and saves as EWS_500_ERRORS.CSV</QueryDescription>
    <QueryData>select 
	cs-username,
	cs(User-Agent) AS EWSclient, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
 	FROM '[LOGFILEPATH]'
	WHERE HTTPstatus LIKE '500.0' AND application LIKE '%ews%' AND cs-username IS NOT NULL
	Group BY cs-username, EWSclient, application, HTTPstatus
	Order BY cs-username, EWSclient</QueryData>
    <QueryID>83c965f1-a143-4c65-b144-a8688286b30f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: 500 Errors [CSV]</QueryName>
    <QueryDescription>Finds EWS HTTP 500 errors and saves as EWS500ERRORS.CSV</QueryDescription>
    <QueryData>select 
	cs-username,
	cs(User-Agent) AS EWSclient, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
 INTO 'EWS500Errors.CSV'
 	FROM '[LOGFILEPATH]'
	WHERE HTTPstatus LIKE '500.0' AND application LIKE '%ews%' AND cs-username IS NOT NULL
	Group BY cs-username, EWSclient, application, HTTPstatus
	Order BY cs-username, EWSclient</QueryData>
    <QueryID>be0e5144-f52d-40eb-95ea-35a8fb2d161e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: All 500s per user, per 15 Minute interval between two times</QueryName>
    <QueryDescription>Find all EWS requests resulting in HTTP 500x between two times. Counted in 15 minute intervals</QueryDescription>
    <QueryData>/*  EWS: Find all EWS requests resulting in HTTP 500x between two times */

SELECT TOP 10000 
TO_STRING(QUANTIZE(TO_TIMESTAMP(date, time), 900), 'hh:mm:ss') as TimeSlice, sc-status, 
cs-username, cs-uri-query
FROM '[LOGFILEPATH]'
WHERE TO_TIME(time) &gt; '01:00:00'
AND TO_TIME(time) &lt; '05:00:00'
AND cs-uri-stem  LIKE '%/ews%' 
AND sc-status &gt; 499 AND sc-status &lt; 600 
GROUP BY cs-username, TimeSlice, sc-status, cs-uri-query
</QueryData>
    <QueryID>78a19781-030c-4dfb-a045-49476136eae6</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Average Bandwidth Second/Hour</QueryName>
    <QueryDescription>Average bandwidth per second every hour</QueryDescription>
    <QueryData>/* Average bandwidth per second/hour */

SELECT 
TO_LOCALTIME(QUANTIZE(time, 3600)) AS [Hour], 
ADD([Server Kb/Sec], [Client Kb/Sec]) AS KBytesSec 
USING DIV(DIV(MUL(1.0, SUM(sc-bytes)), 1024), 3600) AS [Server Kb/Sec], 
DIV(DIV(MUL(1.0, SUM(cs-bytes)), 1024), 3600) AS [Client Kb/Sec] 
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem LIKE '%/EWS%'
GROUP BY [Hour] 
ORDER BY [Hour] ASC</QueryData>
    <QueryID>aee89b9e-a558-497a-b614-1c7f77d8f0ef</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Average Response Time 1/2 Hour</QueryName>
    <QueryDescription>Returns the average response time for EWS request broken down to 1/2 hour segments</QueryDescription>
    <QueryData>/* Average response times every 1/2 hour */

SELECT TO_LOCALTIME(QUANTIZE(time, 1800)) as [1/2 Hour], 
AVG(time-taken) as [Time (ms)] 
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem 
LIKE '%EWS%' 
AND sc-status &lt; 400 
GROUP BY [1/2 Hour] 
ORDER BY [1/2 Hour] ASC</QueryData>
    <QueryID>edeb86c7-c8e9-4571-9cb1-1571e653a14f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: BESAdmin Impersonated Login Report [CSV]</QueryName>
    <QueryDescription>Use this to find who is accessing EWS via BES and what commands they are running.


Ref: http://blogs.technet.com/b/caseys/archive/2012/01/23/troubleshooting-ews-requests-using-iis-logs-with-logparser.aspx</QueryDescription>
    <QueryData>/* EWS: Find all BESAdmin Impersonated Logins */

 SELECT TO_TIMESTAMP(TO_TIME(time), TO_DATE(date)) as TimeStamp,
 Count(*) as Total,
 cs-username as Username, 
 substr(substr(cs-uri-query, INDEX_OF(cs-uri-query, 'Impersonate')),0,INDEX_OF(substr(cs-uri-query, INDEX_OF(cs-uri-query, 'Impersonate')),';')) AS [Impersonate], 
 substr(substr(cs-uri-query, INDEX_OF(cs-uri-query, 'SoapAction')),0,INDEX_OF(substr(cs-uri-query, INDEX_OF(cs-uri-query, 'SoapAction')),';')) AS [SoapAction], 
 substr(substr(cs-uri-query,INDEX_OF(cs-uri-query, 'RC')),0,INDEX_OF(substr(cs-uri-query, INDEX_OF(cs-uri-query, 'RC')),';')) AS [RC Data], 
 cs-uri-stem as URI
 INTO '[OUTFILEPATH]\BESAdminEWSLogins.CSV'
 FROM '[LOGFILEPATH]' 
 Where cs-username LIKE '%BESAdmin%' 
 AND cs-uri-query LIKE '%SoapAction%' 
 GROUP BY TimeStamp,  Username, [Impersonate], [SoapAction], [RC Data], URI
 ORDER BY Total DESC</QueryData>
    <QueryID>db277f7b-b5cf-4d58-b2c5-47a22f115459</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Budget Report [100% Exceeded]</QueryName>
    <QueryDescription>Returns all EWS requests where 100% the budget of any budget is exceeded.</QueryDescription>
    <QueryData>/* 
    Returns all EWS request where any budget is over 100%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
    These users are exceeding their budget.
*/

SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/ews%' 
AND (TO_INT(AD%) &gt; 100 OR TO_INT(CAS%) &gt; 100 OR TO_INT(AB%) &gt; 100 OR TO_INT(RPC%) &gt; 100 OR TO_INT(FC%) &gt; 100)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>797ecf47-b544-4819-9122-051ac956228f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:32.1383202-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Budget Report [75% Exceeded]</QueryName>
    <QueryDescription>All EWS Requests with 75% of any budget was exceeded.</QueryDescription>
    <QueryData>/* 
    Returns all EWS request where any budget is over 75%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
*/

SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/ews%' 
AND (TO_INT(AD%) &gt; 75 OR TO_INT(CAS%) &gt; 75 OR TO_INT(AB%) &gt; 75 OR TO_INT(RPC%) &gt; 75 OR TO_INT(FC%) &gt; 75)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>5eed5dc2-09cf-408f-934f-631363a6945b</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:35.5616745-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Count requests per user per 15 minute interval  between two times </QueryName>
    <QueryDescription>EWS: Count requests per user per 15 minute interval  between two times.</QueryDescription>
    <QueryData>/*  EWS: Count requests per user per 15 minute interval  between two times */

SELECT TOP 10000 Count(*) as NumHits, 
TO_STRING(QUANTIZE(TO_TIMESTAMP(date, time), 900), 'hh:mm:ss') as TimeSlice, 
cs-uri-query,
cs-username
FROM '[LOGFILEPATH]'
WHERE TO_TIME(time) &gt; '00:00:00'
AND TO_TIME(time) &lt; '11:59:59'
AND cs-uri-stem  LIKE '%/ews%' 
GROUP BY TimeSlice, cs-username, cs-uri-query
</QueryData>
    <QueryID>59984068-972e-4b1a-b02b-0d6e79b44b52</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Count users with 503 errors</QueryName>
    <QueryDescription>Count EWS users with 503 errors</QueryDescription>
    <QueryData>/*  Count EWS Users with 503 Errors */
SELECT
	cs-username,
	cs(User-Agent) AS EWSclient, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, 
	Count(*)
	FROM '[LOGFILEPATH]'
	WHERE HTTPstatus LIKE '503' AND application LIKE '%ews%' AND cs-username IS NOT NULL
	Group BY cs-username, EWSclient, application, HTTPstatus
	Order BY cs-username, EWSclient</QueryData>
    <QueryID>13977f45-ed39-4e18-82c3-4accdca95d83</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Exchange 2013 - TOP 1000 EWS requests by CPU Used</QueryName>
    <QueryDescription>Finds the top 1000 entries sorted by highest CPU</QueryDescription>
    <QueryData>/*  Exchange 2013 - Find TOP 1000 EWS requests that use the most CPU  */

SELECT TOP 1000  
ADD(ADD(TO_STRING(date, 'MM/dd/yyyy'), '-'), TO_STRING(time, 'hh:mm:ss')) as [Time], 
cs-username as User,
cs(user-agent) as [Client Application],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Request.CPU.Total='), 0, ';') as [CPU Cost],
cs-uri-query as Querystring
FROM '[LOGFILEPATH]'
WHERE cs-uri-query IS NOT NULL
AND cs-uri-query LIKE '%Request.CPU.Total%' 
AND cs-uri-stem LIKE '%/EWS%' 
ORDER BY [CPU Cost] DESC</QueryData>
    <QueryID>b740a7d5-f120-497e-8d06-a7830eb1b1af</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Exchange 2013 EWS CPU usage per 5 minute interval.</QueryName>
    <QueryDescription>Reports the amount of CPU being used at five minute intervals.</QueryDescription>
    <QueryData>/*  Exchange 2013 - EWS CPU usage per 5 minute interval  */

SELECT TOP 1000 TO_STRING(QUANTIZE(TO_TIMESTAMP(date, time), 10), 'hh:mm:ss') as Time, 
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Request.CPU.Total='), 0, ';') as [CPU Cost] 
FROM 'E:\TestLogs\E15IISLog.log' 
WHERE cs-uri-query IS NOT NULL 
AND cs-uri-query LIKE '%Request.CPU.Total%' 
AND cs-uri-stem LIKE '%/EWS%' ORDER BY [CPU Cost] DESC

/* This is a chart friendly query, just click F6 after the query is complete */</QueryData>
    <QueryID>6ddbbc08-a5d6-40a8-bca1-46d2edaad3c6</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Failed SoapActions by Action/Item/User/Error</QueryName>
    <QueryDescription>Returns all 500 errors returned for users whose EWS Request contains SoapAction</QueryDescription>
    <QueryData>/* 
    Count Failed (HTTP 500x) EWS SoapActions per user and count. 
*/

SELECT 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction, sc-status AS Status, cs-username as UserName,
 Count(*) AS Total 
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; '' 
AND SoapAction &lt;&gt; NULL
AND sc-status &gt; 499
GROUP BY SoapAction, Status, UserName
ORDER BY SoapAction, Total DESC</QueryData>
    <QueryID>d37d2c56-1fcc-4d7c-af81-1378f8b8ce11</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Find all EWS requests between two timeframes excluding 401s</QueryName>
    <QueryDescription>EWS: Find all EWS requests between two timeframes excluding 401s</QueryDescription>
    <QueryData>/*  EWS: Find all EWS requests between two timeframes excluding 401s  */

SELECT TOP 10000  
TO_STRING(time, 'hh:mm:ss') as [Time], cs-username, sc-status, sc-substatus,
time-taken, cs-uri-query
FROM '[LOGFILEPATH]'
WHERE TO_TIME(time) &gt; '01:00:00'
AND TO_TIME(time) &lt; '05:00:00'
AND cs-uri-stem  LIKE '%/ews%' 
AND sc-status &lt;&gt;  401 /* &lt;-- ignoring cred failures remove this entire line to include */
</QueryData>
    <QueryID>cebf7f62-bfc4-4fd5-8e84-7ac25b95726f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: GetOOFSettings Report</QueryName>
    <QueryDescription>Creates a detailed report including latency and budget for GetUserOOFSettings requests.</QueryDescription>
    <QueryData>/* 
    Detailed GetUserOOFSettings Report
*/

SELECT cs-username as User,
 Count(*) AS Total, 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';MailboxRPCRequests='), 0, ';') As MBXReqs,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';MailboxRPCLatency='), 0, ';') As MBXLat,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';ADRequests='), 0, ';') As ADReqs,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';ADLatency='), 0, ';') As ADLat,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';TimeInGetUserOOFSettings='), 0, ';') As OOFTime,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '/Execute:'), 0, ';'), 'msec', '') As Execute,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';End('), 0, ')'), 'ms', '') As End,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '&gt;Conn:'), 0, ','), 'ms', '') As Conn,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ',HangingConn:'), 0, ','), 'ms', '') As HangConn,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Resources:'), 0, '(Health') As Target,
 sc-status as Status,
 sc-substatus as SubStatus,
 sc-Win32-Status as WinErr

FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-uri-query NOT LIKE '%GetEvents%' 
AND cs-uri-query NOT LIKE '%GetMailTips%'
AND cs-uri-query LIKE '%GetUserOofSettingsResponse%'
AND cs-username LIKE /* place username between %% -&gt; */ '%%'
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; ''
GROUP BY cs-username, SoapAction, MBXReqs, MBXLat, ADReqs, ADLat, Execute, End, Conn, HangConn,
OOFTime, Target, Status, SubStatus, WinErr
ORDER BY Total, OOFTime, User, MBXReqs, MBXLat, ADReqs, ADLat Desc</QueryData>
    <QueryID>4e7e2618-542e-4e9b-af3c-47c547c9c0fc</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: HTTP 500 Errors per Hour per User</QueryName>
    <QueryDescription>Returns and counts all EWS Requests per Hour for a single user </QueryDescription>
    <QueryData>/* Returns and counts all EWS Requests per Hour for a single user 
   Be sure to add the username below by replacing REPLACE_WITH_USERNAME
   with the user you wish to search for.
*/

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS hour, 
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir,
	STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus,
 sc-win32-status AS Win32Raw,
 WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Win32Name
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '/EWS/ex%' AND cs-username LIKE '%REPLACE_WITH_USERNAME%'
AND HTTPstatus &lt;&gt; '0000' /* &lt;--change 0000 to 200 to hide 200s etc. */ 
GROUP BY hour, Vdir, HTTPstatus, Win32Raw, Win32Name
ORDER BY hour

</QueryData>
    <QueryID>ed56a28c-a252-4a2d-8709-e38dcdf20b93</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: HTTP 500 Errors per Hour w/Description</QueryName>
    <QueryDescription>Returns and counts all EWS HTTP 500 errors per hour including HTTP Status and underlying Win32 Error Description</QueryDescription>
    <QueryData>/* Returns and counts all EWS HTTP 500 errors per hour including HTTP Status and underlying Win32 Error Description */

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS hour, 
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir,
	STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus,
 sc-win32-status AS Win32Raw,
 WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Win32Name
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '/EWS/ex%' AND HTTPstatus &gt; '499'
GROUP BY hour, Vdir, HTTPstatus, Win32Raw, Win32Name
ORDER BY hour
</QueryData>
    <QueryID>16cf3214-f6f4-49d4-afd8-1897fd76a869</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: PROXY  (500 Errors)</QueryName>
    <QueryDescription>Finds EWS HTTP 500 errors that are CAS Proxy requests</QueryDescription>
    <QueryData>/* Return All EWS Proxy Requests with 500 Errors */

	SELECT cs-username,
	cs(User-Agent) AS EWSclient, 
 TO_LOWERCASE(cs-uri-stem) AS URI, 
 STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus, sc-substatus As SubStatus,
	Count(*)

 FROM '[LOGFILEPATH]'
 WHERE HTTPStatus LIKE '%500%' and cs-uri-stem LIKE '%ExchangeWebServicesProxy%'
 GROUP BY HttpStatus, SubStatus, cs-username, EWSClient, URI
 ORDER BY cs-username ASC</QueryData>
    <QueryID>3a9aa6be-72cc-4e30-9fbe-d817e8740e32</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Requests Every 3 Minutes</QueryName>
    <QueryDescription>Counts the number of EWS requests every 3 minutes and lists them chronologically</QueryDescription>
    <QueryData>/* Returns the number of EWS requests made every 3 minutes chronologically */

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 180) AS threemin, 
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '/EWS/ex%' 
GROUP BY threemin, Vdir
ORDER BY threemin
</QueryData>
    <QueryID>893d42bc-5e7f-4405-b8ff-c7889a4799ee</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Requests Every Hour</QueryName>
    <QueryDescription>Counts the number of EWS requests every hour and lists them chronologically</QueryDescription>
    <QueryData>/* Returns the number of EWS requests made every hour chronologically */

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS hour, 
	COUNT(*) AS Total,  
	TO_LOWERCASE(cs-uri-stem) AS Vdir
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '/EWS%' 
GROUP BY hour, Vdir
ORDER BY hour
</QueryData>
    <QueryID>aef4d04e-9098-40c9-af11-2fdbea36e91f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Requests per Hour</QueryName>
    <QueryDescription>Returns the requests per hour to /EWS. Use this query to see which times of day the most requests are being made.</QueryDescription>
    <QueryData>/* Requests per Hour - Try with Radar Chart [experimental] */

SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)) AS Hour, 
COUNT(*) AS [Hits], 
AVG(time-taken) as [Avg Response Time]
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%/ews%'
GROUP BY Hour 
ORDER BY Hour</QueryData>
    <QueryID>63021028-9e8b-458c-822c-6b02187313c0</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: SOAP Report by SoapAction</QueryName>
    <QueryDescription>Creates a detailed report including latency and budget for all SoapAction requests.</QueryDescription>
    <QueryData>/* 
    EWS report by SOAP requests. 
*/

SELECT cs-username as User,
 Count(*) AS Total, 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '/Execute:'), 0, ';'), 'msec', '') As Execute,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ';End('), 0, ')'), 'ms', '') As End,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, '&gt;Conn:'), 0, ','), 'ms', '') As Conn,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, ',HangingConn:'), 0, ','), 'ms', '') As HangConn,
 time-taken as ResponseTime,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Resources:'), 0, '(Health') As Target,
 cs(user-agent) as Device
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-uri-query NOT LIKE '%GetEvents%' 
AND cs-uri-query NOT LIKE '%GetMailTips%'
AND cs-uri-query NOT LIKE '%GetUserOofSettingsResponse%'
AND cs-username LIKE /* place username between %% -&gt; */ '%%'
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; ''
GROUP BY cs-username, SoapAction, Execute, End, Conn, HangConn, Target, Responsetime, Device
ORDER BY Total, ResponseTime, User DESC</QueryData>
    <QueryID>694d4947-fc06-4c94-9e76-58e0757f720e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: SoapActions by Action/Item/User</QueryName>
    <QueryDescription>Returns all EWS requests containing SoapAction, counts them per action and user making the request.</QueryDescription>
    <QueryData>/* 
    Count EWS SoapActions per user and count. 

*/

SELECT 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction, sc-status AS Status, cs-username as UserName,
 Count(*) AS Total 
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; '' 
AND SoapAction &lt;&gt; NULL
GROUP BY SoapAction, Status, UserName
ORDER BY SoapAction, Total DESC</QueryData>
    <QueryID>48c705cb-e923-478c-a0a1-3b3175b06469</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: SoapActions by Action/Item/User [CSV]</QueryName>
    <QueryDescription>Returns all EWS requests containing SoapAction, counts them per action and user making the request.</QueryDescription>
    <QueryData>/* 
    Count EWS SoapActions per user and count. 
    Send to CSV file.
*/

SELECT 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction, sc-status AS Status, cs-username as UserName,
 Count(*) AS Total 
INTO '[OUTFILEPATH]\EWSSoapActionsPerUser.CSV'
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; '' 
AND SoapAction &lt;&gt; NULL
GROUP BY SoapAction, Status, UserName
ORDER BY SoapAction, Total DESC</QueryData>
    <QueryID>d4fc29cd-97d1-4f4e-800a-b6604d96a9ce</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: SoapActions by Action/Item/User [Failures]</QueryName>
    <QueryDescription>Returns all SoapAction 500x errors.</QueryDescription>
    <QueryData>/* 
    Count Failed (HTTP 500x) EWS SoapActions per user and count. 
*/

SELECT 
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction, sc-status AS Status, cs-username as UserName, cs(user-agent) as Client,
 Count(*) AS Total 
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%ews%' 
AND cs-username &lt;&gt; NULL
AND SoapAction &lt;&gt; '' 
AND SoapAction &lt;&gt; NULL
AND sc-status &gt; 499
GROUP BY SoapAction, Status, UserName, Client
ORDER BY Total, UserName DESC</QueryData>
    <QueryID>9743dd84-2752-4599-9960-232acf54e438</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Top 20 EWS Users</QueryName>
    <QueryDescription>Find the top 20 EWS users by total number of requests</QueryDescription>
    <QueryData>SELECT TOP 20 cs-username AS UserID, 
	cs(User-Agent) AS Application, 
	cs-uri-stem AS Vdir,
	c-ip AS CLIENT,
	cs-method,
	Count(*)
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%EWS%'
GROUP BY UserID, Application, Vdir, Client, cs-method
ORDER BY COUNT(*) DESC</QueryData>
    <QueryID>378e1075-7526-44d9-8608-58a36c8ffe3c</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Top 20 EWS Users - Min/Max/Avg</QueryName>
    <QueryDescription>Find the top 20 EWS users by total number of requests and include slowest, fastest and average request times</QueryDescription>
    <QueryData>SELECT TOP 20 cs-username AS UserID, 
  MAX(time-taken) as [Slowest Time (ms)],  
  MIN(time-taken) as [Fastest Time (ms)], 
  AVG(Time-taken) as [Average Time (ms)], 
  SUM(time-taken) as [Total Time Used (ms)],
 	Count(*) as Requests,
 	cs(User-Agent) AS Application, 
 	cs-method as Method
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%EWS%'
GROUP BY UserID, Application, cs-method
ORDER BY [Slowest Time (ms)], Requests DESC</QueryData>
    <QueryID>d97856ad-0a83-4561-87a3-931854d129f0</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Top 20 EWS Users CSV</QueryName>
    <QueryDescription>Find the top 20 EWS users by total number of requests</QueryDescription>
    <QueryData>SELECT TOP 20 cs-username AS UserID, 
	cs(User-Agent) AS Application, 
	cs-uri-stem AS Vdir,
	c-ip AS CLIENT,
	cs-method,
	Count(*)
INTO '[OUTFILEPATH]\TOP20.CSV'
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%EWS%'
GROUP BY UserID, Application, Vdir, Client, cs-method
ORDER BY COUNT(*) DESC</QueryData>
    <QueryID>4a2df592-850c-415c-8ad3-c6a8f688f793</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EWS: Top 5 EWS Users</QueryName>
    <QueryDescription>Finds the top 5 EWS users and lists them</QueryDescription>
    <QueryData>SELECT TOP 5 cs-username AS UserID, 
	cs(User-Agent) AS DeviceType, 
	cs-uri-stem AS Vdir,
	c-ip AS CLIENT,
	cs-method,
	Count(*)
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%/EWS%' AND cs-method LIKE 'POST'
GROUP BY UserID, DeviceType, Vdir, Client, cs-method
ORDER BY COUNT(*) DESC</QueryData>
    <QueryID>6b98815e-1de5-4408-bdcd-638cce83188a</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>ExRCA: Requests</QueryName>
    <QueryDescription>Finds all requests made by the Exchange Remote Connectivity Analyzer (testexchangeconnectivity.com)</QueryDescription>
    <QueryData>/* Find all ExRCA test requests; due to limitations this cannot find RPC/HTTP requests or EWS Test */
/* Because of this it is likely better to use one of the username queries from the libary and search for the user conducting the test */

SELECT date, time, cs-username, c-ip, s-ip, cs-uri-stem, cs-uri-query, sc-status, sc-win32-status, time-taken
FROM '[LOGFILEPATH]'
WHERE cs(user-agent) LIKE '%TestExchangeConnectivity.com%' 

</QueryData>
    <QueryID>3c62179f-dc13-4ad9-8d70-a88f6a7741df</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EXRPC: Find Exchange RPC Client Access Log Field Names</QueryName>
    <QueryDescription>Returns the field names for Exchange RPC Client Access Logs. Use this query when writing your own queries and need to know the names of the fields. These are the static field names as defined in the LPCustomInputFormats Plugin.</QueryDescription>
    <QueryData>/*  Get field names for Exchange RPC Client Access Logs */

SELECT TOP 0 * 
FROM '[LOGFILEPATH]' 
WHERE date-time IS NOT NULL





	</QueryData>
    <QueryID>f0054b57-8830-4fd9-9849-f9196f733326</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:38:31.7266575-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EXRPC: Find requests with rpc-status or failures &gt; 0</QueryName>
    <QueryDescription>Sample query for Exchange RPC Client Access Logs</QueryDescription>
    <QueryData>/*  Find all requests where rpc-stats &gt; 0 or Failures &gt; 0 */

SELECT TOP 5000 * 
FROM '[LOGFILEPATH]' 
WHERE date-time IS NOT NULL
AND rpc-status NOT LIKE '0'
OR Failures &gt; 0



	</QueryData>
    <QueryID>d6ed9aae-3861-429c-811d-87670dedaca0</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>EXSMTP: Sample SmtpSend Query</QueryName>
    <QueryDescription>Sample SmtpSend Query for Exchange 2010 SmtpSend protocol logging

</QueryDescription>
    <QueryData>/*  Sample Exchange 2010 SmtpSend Logs */

SELECT TOP 500 * 
FROM '[LOGFILEPATH]' 
WHERE date-time IS NOT NULL



	</QueryData>
    <QueryID>4a8cff77-ea25-4c57-868f-b9f7cafe0895</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>FileSystem: Sample query lists properties for all LPS files.</QueryName>
    <QueryDescription>Lists all folders and files in the LPS directory along with their properties. </QueryDescription>
    <QueryData>/*  Sample FSLOG Query: List all files/folders in the LPS install folder and their properties  */

SELECT Name, Size, CreationTime, LastAccessTime, LastWriteTime, 
FileVersion, ProductName FROM '*.*'
WHERE NAME NOT LIKE '.%' 
ORDER BY Name ASC</QueryData>
    <QueryID>42c2122d-7ca3-420e-87e8-4e57910d69aa</QueryID>
    <LogType>FSLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T22:14:25.1882946-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Find ActiveSync User</QueryName>
    <QueryDescription>Find MAS user by User Name. Be sure to add the user name to the query</QueryDescription>
    <QueryData>SELECT TOP 3 cs-username AS UserID, 
	cs(User-Agent) AS DeviceType, 
	EXTRACT_TOKEN(cs-uri-query,0,'|') AS Query,
	cs-uri-stem AS Vdir,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
	cs-method AS VERB, 
	s-ip AS SERVER,
	c-ip AS CLIENT,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
INTO '[OUTFILEPATH]\findASuser.csv'
FROM *
WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-username LIKE /* Put username here --&gt; */'%johndoe%'
GROUP BY GMTTime, HTTPstatus, UserID, Server, DeviceType, VERB, Query, Vdir, Client
ORDER BY GMTTime, UserID, client, deviceType, VERB, Vdir, Query, HTTPstatus DESC</QueryData>
    <QueryID>65116cbb-b8fd-4d1e-892c-a719c54ba9f5</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MAS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Find EWS Delays by User [CSV]</QueryName>
    <QueryDescription>Be sure to add the user name to the query</QueryDescription>
    <QueryData>/* Finds all users with "Delay" in the response, then reports all relevant throttling indicators */

SELECT cs-username AS UserID, 
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'UserDelay:'), 0, ';') AS UserDelay,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'UserDelayCnt:'), 0, ';') AS UserDelayCount,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 	cs(User-Agent) AS DeviceType, 
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'Policy:'), 0, ',') AS Policy,
	cs-uri-stem AS Vdir,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
	s-ip AS SERVER,
	c-ip AS CLIENT,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime,
 cs-uri-query
INTO '[OUTFILEPATH]\EWSUserDelays.CSV'
FROM '[LOGFILEPATH]'
WHERE cs-uri-query LIKE '%Delay%' 
AND cs-uri-stem LIKE '%EWS%'
GROUP BY UserID, GMTTime, HTTPstatus, Server, DeviceType, UserDelay, Vdir, Client, cs-uri-query
ORDER BY UserID, GMTTime, UserID, client, deviceType, Vdir, UserDelay, HTTPstatus DESC</QueryData>
    <QueryID>6d3127cf-c790-40dc-a065-b9c6340b3b0e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Find EWS Users w/TimeOutExpired Errors</QueryName>
    <QueryDescription>Find Throttled EWS Users</QueryDescription>
    <QueryData>SELECT
	cs-username,
	cs(User-Agent) AS EWSclient, 
    TO_LOWERCASE(cs-uri-stem) AS application, 
	TO_LOWERCASE(cs-uri-query) AS query,
	Count(*)
FROM '[LOGFILEPATH]'
WHERE query LIKE '%ErrorTimeoutExpired%' AND application LIKE '%ews%'
Group BY cs-username, EWSclient, application, query
Order BY cs-username, EWSclient</QueryData>
    <QueryID>cc1cdbc1-1684-4e85-b9c1-b801568e7a4c</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>EWS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>FS: IIS Log File Sizes Last 10 Days</QueryName>
    <QueryDescription>You can use this query to measure server activity over that last 10 days based on IIS log file sizes. Larger files = more requests. If you have an NLB run 1 query tab per server to confirm load distribution. The closer the file sizes are between servers, the better the NLB distribution.</QueryDescription>
    <QueryData>/*  Compare file sizes of the last 10 days of IIS logs*/

SELECT TOP 10 Path, Size, LastWriteTime FROM '[LOGFILEPATH]'
ORDER BY LastWriteTime ASC

/*  

LOGFILEPATH should point to an IIS log folder 
If you have an NLB run 1 query tab per server to confirm load distribution 
The better the distribution, the closer the file sizes between servers.

*/
</QueryData>
    <QueryID>7a08826e-08c8-46d9-b0ed-12efad1971cf</QueryID>
    <LogType>FSLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>FS: IIS Log File Sizes Last 10 Days</QueryName>
    <QueryDescription>You can use this query to measure server activity over that last 10 days based on IIS log file sizes. Larger files = more requests. If you have an NLB run 1 query tab per server to confirm load distribution. The closer the file sizes are between servers, the better the NLB distribution.</QueryDescription>
    <QueryData>/*  Compare file sizes of the last 10 days of IIS logs*/

SELECT TOP 10 Path, Size, LastWriteTime FROM '[LOGFILEPATH]'
ORDER BY LastWriteTime ASC

/*  
LOGFILEPATH should point to an IIS log folder 
*/
</QueryData>
    <QueryID>7a08826e-08c8-46d9-b0ed-12efad1971cf</QueryID>
    <LogType>FSLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T22:15:20.0973935-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Abandoned Requests</QueryName>
    <QueryDescription>Find times any applicationpool or request queue has abandoned a connection per hour.</QueryDescription>
    <QueryData>/*  
    Find times any applicationpool or request queue has abandoned a connection per hour.
*/

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS DateTime, 
	COUNT(*) AS Total, 
 s-reason AS Reason,
 cs-version AS [Client Version],
 cs-method AS Method,
 s-queuename as AppPool,
 LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Connection_Abandoned_By_AppPool%'
OR    s-reason LIKE '%Connection_Abandoned_By_ReqQueue%'
GROUP BY  DateTime, Reason, [Client Version], Method, AppPool, LogFilename
ORDER BY  DateTime ASC</QueryData>
    <QueryID>cdff6cb3-20d6-4225-8556-a31fd97803ad</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:19.7793875-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Abandoned Requests - Specific AppPool</QueryName>
    <QueryDescription>Finds times any application pool or request queue has abandoned a connection per hour for the specific application pool used as search criteria in the query.</QueryDescription>
    <QueryData>/*  
    Find times any applicationpool or request queue has abandoned a connection per hour
    for the specific appPool. The default is DefaultAppPool
*/

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS DateTime, 
	COUNT(*) AS Total, 
 s-reason AS Reason,
 cs-version AS [Client Version],
 cs-method AS Method,
 s-queuename as AppPool,
 LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Connection_Abandoned_By_AppPool%'
OR    s-reason LIKE '%Connection_Abandoned_By_ReqQueue%'

AND AppPool LIKE '%DefaultAppPool%' /* &lt;- Replace DefaultAppPool with the pool you wish to find */

GROUP BY  DateTime, Reason, [Client Version], Method, AppPool, LogFilename
ORDER BY  DateTime ASC</QueryData>
    <QueryID>611d3bc7-7231-4ad5-bd65-08a8a464fba3</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:26.551368-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: AppPool Crashes per 1/2 Hour</QueryName>
    <QueryDescription>Finds the number of times any application pool crashes or goes off line every 1/2 hour.</QueryDescription>
    <QueryData>/*  
   Find times an applicationpool has went off line due to unexpected 
   shutdown or crash every 1/2 hour
*/

SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 1800) AS DateTime, 
	COUNT(*) AS Total, 
 s-reason AS Reason,
 cs-version AS [Client Version],
 cs-method AS Method,
 s-queuename as AppPool,
 LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%AppOffline%'
GROUP BY  DateTime, Reason, [Client Version], Method, AppPool, LogFilename
ORDER BY  DateTime ASC

</QueryData>
    <QueryID>2fb5179e-eafa-4dd4-ba9b-914b19199c7f</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:32.6699337-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Client_Reset Report</QueryName>
    <QueryDescription>Find all entries of Client_Reset. The connection between the client and the server was closed before the request could be assigned to a worker process. The most common cause of this behavior is that the client prematurely closes its connection to the server. 

See all HTTP Error definitions here: http://support.microsoft.com/kb/820729#3</QueryDescription>
    <QueryData>/*  Find all entries of Client_Reset.
    The connection between the client and the server was closed before the request could be assigned to a worker process. 
    The most common cause of this behavior is that the client prematurely closes its connection to the server. 
*/

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp,
c-ip AS [Client IP],
s-ip AS [Server IP],
cs-version AS [Client Version],
cs-method AS Method,
cs-uri AS Uri,
s-queuename as AppPool,
LogFileName
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Client_Reset%'
ORDER BY Timestamp ASC</QueryData>
    <QueryID>795967df-0699-4d05-b596-7af64fb2c18e</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:38.8349534-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Common Http.Sys Errors</QueryName>
    <QueryDescription>Finds the most common HTTP Errors.
The definitions for the errors can be found here: http://support.microsoft.com/kb/820729#3</QueryDescription>
    <QueryData>/*  
    Find the most commonly occurring HTTP Errors
    Reason Definitions can be found here: http://support.microsoft.com/kb/820729#3 
*/

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp,
s-reason AS Reason,
c-ip AS [Client IP],
s-ip AS [Server IP],
cs-version AS [Client Version],
cs-method AS Method,
cs-uri AS Uri,
s-queuename as AppPool,
LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Timer_AppPool%'
OR s-reason LIKE '%Timer_EntityBody%'
OR s-reason LIKE '%QueueFull%'
OR s-reason LIKE '%Forbidden%'
OR s-reason LIKE '%RequestLength%'
OR s-reason LIKE '%Precondition%'
OR s-reason LIKE '%Timer_MinBytesPerSecond%'
OR s-reason LIKE '%ConnLimit%'
OR s-reason LIKE '%Connections_Refused%'
OR s-reason LIKE '%Connection_Abandoned_By_ReqQueue%'
OR s-reason LIKE '%Connection_Abandoned_By_AppPool%'
OR s-reason LIKE '%Hostname%'
OR s-reason LIKE '%EntityTooLarge%'
OR s-reason LIKE '%AppPoolTimer%'
ORDER BY Timestamp, Reason ASC</QueryData>
    <QueryID>8e50bd76-587d-40ea-ace6-1fd9468b843a</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:49.3530491-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Connections_Refused</QueryName>
    <QueryDescription>The kernel NonPagedPool memory has dropped below 20MB and http.sys has stopped receiving new connections.</QueryDescription>
    <QueryData>/*  
    The kernel NonPagedPool memory has dropped below 20MB and http.sys has stopped receiving new connections. 
*/

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp,
c-ip AS [Client IP],
s-ip AS [Server IP],
cs-version AS [Client Version],
cs-method AS Method,
cs-uri AS Uri,
s-queuename as AppPool,
LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Connections_Refused%'
ORDER BY Timestamp ASC</QueryData>
    <QueryID>9501dba5-9454-4f68-b339-ae45848ee61e</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:54.0545833-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Find App Pool Crashes</QueryName>
    <QueryDescription>Find times an applicationpool has went off line due to unexpected shutdown or crash.</QueryDescription>
    <QueryData>/*  
   Find times an applicationpool has went off line due to unexpected shutdown or crash.
*/

SELECT	TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS DateTime, 
 s-reason AS Reason,
 c-ip AS [Client IP],
 s-ip AS [Server IP],
 cs-version AS [Client Version],
 cs-method AS Method,
 cs-uri AS Uri,
 s-queuename as Queue,
 LogFilename AS LogFile
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%AppOffline%'
GROUP BY  DateTime, Reason, [Client IP], [Server IP], [Client Version], Method, Uri, Queue, LogFile
ORDER BY  DateTime ASC

</QueryData>
    <QueryID>92eef2f9-7eb9-44ec-9e49-58e88664989a</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:54:59.1392815-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Queue_Full</QueryName>
    <QueryDescription>A service unavailable error occurred (an HTTP error 503). The service is not available because the application request queue is full.</QueryDescription>
    <QueryData>/*  
    A service unavailable error occurred (an HTTP error 503). The service is not available because the application request queue is full. 
*/

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp,
c-ip AS [Client IP],
s-ip AS [Server IP],
cs-version AS [Client Version],
cs-method AS Method,
cs-uri AS Uri,
s-queuename as AppPool,
LogFilename
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%QueueFull%'
ORDER BY Timestamp ASC</QueryData>
    <QueryID>10e5d624-98c8-4008-8635-27632e7f2a27</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:55:03.4708778-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>HTTPERR: Timer_AppPool</QueryName>
    <QueryDescription>The connection expired because a request waited too long in an application pool queue for a server application to dequeue and process it. This timeout duration is ConnectionTimeout. By default, this value is set to two minutes.</QueryDescription>
    <QueryData>/*  
   The connection expired because a request waited too long in an application pool queue for a server application to dequeue and process it.          This timeout duration is ConnectionTimeout. By default, this value is set to two minutes.. 
*/

SELECT TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp,
    c-ip AS [Client IP],
    s-ip AS [Server IP],
    cs-version AS [Client Version],
    cs-method AS Method,
    cs-uri AS Uri,
    s-queuename as AppPool,
LogFileName
FROM '[LOGFILEPATH]'
WHERE s-reason LIKE '%Timer_AppPool%'
ORDER BY Timestamp ASC</QueryData>
    <QueryID>cbc91335-87b8-4661-95ae-7818b5367738</QueryID>
    <LogType>HTTPERRLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T20:55:08.3757977-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: All Requests, Specific User [CSV]</QueryName>
    <QueryDescription>Pull all requests for a single user and send to a CSV file.</QueryDescription>
    <QueryData>/* Pull all requests by a single user and send to CSV */
/* If an error is returned there may be no matces */

SELECT date, time, cs-username, sc-status, sc-win32-status, time-taken, cs-uri-query 
INTO '[OUTFILEPATH]\SingleUserActivity.CSV'
FROM '[LOGFILEPATH]' 
WHERE cs-username LIKE '%REPLACE_WITH_USERNAME%' 
ORDER BY time-taken</QueryData>
    <QueryID>d3a0c799-a35f-4727-80a5-26b2ddacb6e6</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T21:41:19.9408239-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Count all Web Service 500 Errors by URI</QueryName>
    <QueryDescription>Counts all errors of calls made to any .NET Web Service</QueryDescription>
    <QueryData>/* All 500 errors to any IIS/.NET Web Service */

SELECT	cs-uri-stem as Uri,
 sc-status as HttpStatus,
 sc-substatus as SubStatus,
 sc-win32-status as Win32Status,
	COUNT(*) AS Total 
FROM '[LOGFILEPATH]' 
WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asmx') 
GROUP BY Uri, HttpStatus, SubStatus, Win32Status 
ORDER BY Total DESC
</QueryData>
    <QueryID>57751a8c-e64d-4f54-a391-89880fbfe3b0</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: HTTP 401 by User [CSV]</QueryName>
    <QueryDescription>HTTP 401 by User </QueryDescription>
    <QueryData>/* All 401 errors counted per user and saved to CSV */

SELECT cs-username, 
	sc-status,
	cs-uri-stem,
	Count(*) AS Total
 INTO '[OUTFILEPATH]\401_username.csv'
	From '[LOGFILEPATH]'
	Where sc-status = 401
	Group By cs-username, sc-status, cs-uri-stem
	order by cs-username
	</QueryData>
    <QueryID>736db969-30e7-4252-9de0-45c7d19ab00d</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: HTTP 503 Errors [CSV]</QueryName>
    <QueryDescription>Returns all 503 errors by user to a CSV file named 503_BY_Username.csv.</QueryDescription>
    <QueryData>SELECT cs-username, 
	sc-status,
	cs-uri-stem,
	Count(*) AS Total
 INTO '[OUTFILEPATH]\503_BY_Username.csv'
	From '[LOGFILEPATH]'
	Where sc-status = 503
	Group By cs-username, sc-status, cs-uri-stem, cs-uri-query
	order by cs-username
	</QueryData>
    <QueryID>6f6fab51-5782-4a04-a16a-5b393de584b9</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: HTTP Status Codes by Count</QueryName>
    <QueryDescription>Returns all status codes and how many times each one occurred.</QueryDescription>
    <QueryData>/*  Count and sort all HTTP status codes */

SELECT TOP 25  
    STRCAT(TO_STRING(sc-status),  
    STRCAT('.', TO_STRING(sc-substatus))) As Status,  
    COUNT(*) AS Hits  
    FROM '[LOGFILEPATH]'  
GROUP BY Status  
ORDER BY Hits DESC </QueryData>
    <QueryID>e80a8693-c0be-49fd-bcab-7e8f6512cfd7</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Request per Hour</QueryName>
    <QueryDescription>RequestsPerHour description</QueryDescription>
    <QueryData>SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, 
	COUNT(*) AS Total,  
	SUM(sc-bytes) AS TotBytesSent 
FROM '[LOGFILEPATH]'
GROUP BY Hour 
ORDER BY Hour
</QueryData>
    <QueryID>451fac87-f83c-4d7d-b8ac-c443852f9ad5</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Request per Hour by Bytes Sent</QueryName>
    <QueryDescription>RequestsPerHour description</QueryDescription>
    <QueryData>SELECT	QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, 
	COUNT(*) AS Total,  
	SUM(sc-bytes) AS TotBytesSent 
FROM '[LOGFILEPATH]'
GROUP BY Hour 
ORDER BY Hour
</QueryData>
    <QueryID>8f89f3ce-6a43-4e94-a1b8-4c5ba3f2d507</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Status/SubStatus Report</QueryName>
    <QueryDescription>StatusSubStatusCount description</QueryDescription>
    <QueryData>SELECT 	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status, 
	COUNT(*) AS Total 
FROM '[LOGFILEPATH]' 
GROUP BY Status 
ORDER BY Total DESC
</QueryData>
    <QueryID>62e69b30-4046-4eaf-807e-537211945ae2</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Top 20 HTTP Verbs</QueryName>
    <QueryDescription>Top 20 Verbs</QueryDescription>
    <QueryData>SELECT TOP 20 	cs-method, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime, 
		AVG(sc-bytes) AS AvgBytesSent 
FROM '[LOGFILEPATH]' 
GROUP BY cs-method 
ORDER BY Total DESC

</QueryData>
    <QueryID>4c13c624-8d15-43b9-b94b-123283467941</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Top 20 URIs</QueryName>
    <QueryDescription>Top 20 URIs requested</QueryDescription>
    <QueryData>SELECT TOP 20 	cs-uri-stem, 
		COUNT(*) AS Total, 
		MAX(time-taken) AS MaxTime, 
		AVG(time-taken) AS AvgTime 
	/* bytes--&gt;	AVG(sc-bytes) AS AvgBytes */
FROM '[LOGFILEPATH]' 
GROUP BY cs-uri-stem
ORDER BY Total DESC
</QueryData>
    <QueryID>2818b23a-5cbe-4e8d-b629-0263313bc4eb</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Top 25 Slow URLs</QueryName>
    <QueryDescription>TOP 25 Slowest URLsl requests incling Min/Max/Average</QueryDescription>
    <QueryData>/*  TOP 25 Slowest Url requests */

SELECT TOP 25  
    cs-uri-stem as URL,  
    MAX(time-taken) As Max,  
    MIN(time-taken) As Min,  
    Avg(time-taken) As Average  
    FROM '[LOGFILEPATH]'  
GROUP BY URL  
ORDER By Average DESC </QueryData>
    <QueryID>8cf173d8-ba44-4aed-ba8c-eaf7f7ce0559</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: User-Agent Report</QueryName>
    <QueryDescription>Find all User-Agents and count them.</QueryDescription>
    <QueryData>/* User-Agent Report */

SELECT distinct cs(User-Agent), count(*) as hits FROM '[LOGFILEPATH]' GROUP BY cs(user-agent) ORDER BY hits DESC</QueryData>
    <QueryID>b7e18f3f-b656-4fbd-b3db-21d1b29c1b7f</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: VDir Hits by IP</QueryName>
    <QueryDescription>Hits on a particular virtual directory by IP address </QueryDescription>
    <QueryData>/* Hits on a particular page by IP address */

select c-ip, count(c-ip) as requestcount from '[LOGFILEPATH]' where cs-uri-stem like '%/%' group by c-ip order by count(c-ip) desc</QueryData>
    <QueryID>daf7031a-c803-4b69-951a-f48fbf6752df</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>IIS: Win32 Error Report</QueryName>
    <QueryDescription>List only Win32 Error codes. Win32 Error codes are errors that were returned to IIS by the OS or other application.</QueryDescription>
    <QueryData>/*  List only Win32 Error codes. Win32 Error codes are errors that 
     were returned to IIS by the OS or other application.  */

SELECT  
    sc-win32-status As Win32-Status, 
    COUNT(*) AS Hits,  
    WIN32_ERROR_DESCRIPTION(sc-win32-status) as Description
    FROM '[LOGFILEPATH]'  
WHERE Win32-Status&lt;&gt;0  
GROUP BY Win32-Status  
ORDER BY Hits DESC</QueryData>
    <QueryID>2b2e7d4b-0a9e-4f48-89fb-d4e5bdc0d2ce</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>LPS: Search Library XML</QueryName>
    <QueryDescription>You can use this query to search the LPS library itself if you need an advanced method of searching queries.</QueryDescription>
    <QueryData>/*  Search the LPS Library for data. This query will find all ActiveSync queries that contain the time-taken field  */

SELECT TOP 10 * FROM '%AppData%\ExLPT\Log Parser Studio\LPQueryLibrary.xml'
WHERE QueryData LIKE '%cs-username%' 
AND QueryName LIKE '%ActiveSync%' 
AND QueryData LIKE '%time-taken%' </QueryData>
    <QueryID>3021875f-1587-41e4-be37-bfbba4149424</QueryID>
    <LogType>XMLLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T11:29:17.7474324-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>LPS: Search Library XML</QueryName>
    <QueryDescription>You can use this query to search the LPS library itself if you need an advanced method of searching queries.</QueryDescription>
    <QueryData>/*  Search the LPS Library for data. This query will find all ActiveSync queries that contain the time-taken field  */

SELECT TOP 10 * FROM '%AppData%\ExLPT\Log Parser Studio\LPQueryLibrary.xml'
WHERE QueryData LIKE '%cs-username%' 
AND QueryName LIKE '%ActiveSync%' 
AND QueryData LIKE '%time-taken%' </QueryData>
    <QueryID>3021875f-1587-41e4-be37-bfbba4149424</QueryID>
    <LogType>XMLLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T11:29:17.7474324-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Delivery Failures by Percentage</QueryName>
    <QueryDescription>Exchange 2010 - Failures by Percentage</QueryDescription>
    <QueryData>/* Delivery failures by percentage */

SELECT TOP 1000  
SUBSTR(recipient-status, 0, 10) AS Status,
TO_REAL(SUBSTR(TO_STRING(MUL(PROPCOUNT(*), 100.0)), 0, 5)) as [Percent],
SUBSTR(recipient-status, 10, STRLEN(recipient-status)) as Description
FROM '[LOGFILEPATH]'
WHERE event-id LIKE '%FAIL%'
AND status IS NOT NULL 
GROUP BY recipient-status
ORDER BY [Percent] DESC

/* 

[Message Tracking 2010 Field Names]

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>ddce55f7-59fd-47f4-be92-f15f6835b518</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:31:34.4224372-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Failures by sender/receiver/direction/status</QueryName>
    <QueryDescription>Exchange 2010 - Failures by sender/receiver/direction/status</QueryDescription>
    <QueryData>/* Failures by sender/receiver/direction/status  */

SELECT TOP 100
Count(*) AS Events,
directionality, sender-address, recipient-address, recipient-status
FROM '[LOGFILEPATH]'
WHERE event-id LIKE '%FAIL%' 
GROUP BY directionality, sender-address,  recipient-address, recipient-status
ORDER BY Events DESC

/* 

Message Tracking 2010 Field Names

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>992aadc7-6b8c-43c5-8e4d-a89a631d29f9</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T13:36:35.8137676-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Failures per Failure/Direction</QueryName>
    <QueryDescription>Exchange 2010 - Failures per Failure/Direction</QueryDescription>
    <QueryData>/*  New Query  */

SELECT TOP 100
Count(*) AS Events,
directionality, sender-address, recipient-status

FROM '[LOGFILEPATH]'
WHERE event-id LIKE '%FAIL%' 
GROUP BY directionality, sender-address, recipient-status

/* 

Message Tracking 2010 Field Names

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>c6b7cec5-7ffd-4e76-9001-9b27e5cd11cb</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T13:32:13.5717682-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Mesage Subject by Count </QueryName>
    <QueryDescription>Mesage Subject by Count </QueryDescription>
    <QueryData>/*  Mesage Subject by Count  */

SELECT 
Count(*) as Receives,
message-subject as Subjects
INTO '[OUTFILEPATH]\Output.CSV' 
FROM '[LOGFILEPATH]'
GROUP BY Subjects
ORDER BY Receives DESC

/* 

[Message Tracking 2010 Field Names]

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>770464e7-82ef-4e4f-b26c-1ed647c16210</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:24:04.0906797-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Messages in pipeline per 1/2 hour</QueryName>
    <QueryDescription>Top Talker by 1/2 hour. Change the quantize interval to another value to change the interval. 1800=1/2 hour, 3600 = 1 hour, 60=1 minute, 300=5 minutes</QueryDescription>
    <QueryData>/*  Exchange 2010 Message Tracking: Messages in pipeline per 1/2 hour  */
/*  Exports to CSV */

SELECT 
QUANTIZE(TO_TIMESTAMP(EXTRACT_PREFIX(EXTRACT_SUFFIX([#Fields: date-time], 0, 'T'), 0, '.'), 'hh:mm:ss'), 1800) As Time, 
Count(*) As PipelineEvents
FROM '[LOGFILEPATH]' 
GROUP BY Time
  

/* 

[Message Tracking 2010 Field Names]

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>fd961ba4-ed3b-4995-b0d1-31c7d41ccacf</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:31:11.1741075-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Messages Received by Count</QueryName>
    <QueryDescription>Mesags received by recipient/count</QueryDescription>
    <QueryData>/*  Messages Received by Count  */

SELECT  
recipient-address as Receiver,
Count(*) as Receives
INTO '[OUTFILEPATH]\Output.CSV' 
FROM '[LOGFILEPATH]'
WHERE directionality LIKE '%Originating%' 
GROUP BY Receiver
ORDER BY Receives DESC

/* 

[Message Tracking 2010 Field Names]

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>8b824e9f-f01b-4046-a690-59d4345e5a91</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:25:40.6192008-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Top Talkers by Sender/Send Source</QueryName>
    <QueryDescription>Top Talkers by Sender/Send Source</QueryDescription>
    <QueryData>/*  Exchange 2010 Message Tracking: Top Talkers by Sender/Send Source */


SELECT Top 1000 
Count(*) AS [Send Count], 
sender-address AS Sender,
source as [Send Source]
FROM '[LOGFILEPATH]'
GROUP BY sender-address, source
ORDER BY [Send Count] DESC

/* Message Tracking 2010 Field Names

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>75fdfc5c-e7f4-463c-8e11-d19130149580</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:31:21.253684-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 - Top Ten Senders</QueryName>
    <QueryDescription>Exchange 2010 - Top Ten Senders</QueryDescription>
    <QueryData>/*  Top 10 Senders  */

SELECT  
recipient-address as Receiver,
Count(*) as Receives
INTO '[OUTFILEPATH]\Output.CSV' 
FROM '[LOGFILEPATH]'
WHERE directionality LIKE '%Originating%' 
GROUP BY Receiver
ORDER BY Receiver DESC

/* 

[Message Tracking 2010 Field Names]

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>9dccf31e-07c0-459e-bec2-97de79ed3c24</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:22:16.2705127-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 -Top Talkers by percentage</QueryName>
    <QueryDescription>Top Talker by percentage</QueryDescription>
    <QueryData>/*  Exchange 2010 Message Tracking: Top Talkers by percentage */


SELECT TOP 1000  sender-address AS Sender,
MUL(PROPCOUNT(*), 100.0) as [Percent Of All Sends]
FROM '[LOGFILEPATH]'
GROUP BY  Sender 
ORDER BY [Percent Of All Sends] DESC

/* Message Tracking 2010 Field Names

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>efdae389-37c7-4a33-9929-704a2957ffdc</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T12:49:30.1681499-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Exchange 2010 -Top Talkers by Sender</QueryName>
    <QueryDescription>Top Talkers by Sender</QueryDescription>
    <QueryData>/*  Exchange 2010 Message Tracking: Top Talkers by Sender  */


SELECT Top 10000 
Count(*) AS [Send Count], 
sender-address AS Sender

FROM '[LOGFILEPATH]'
GROUP BY sender-address
ORDER BY [Send Count] DESC

/* Message Tracking 2010 Field Names

Filename, RowNumber, #Fields: date-time, client-ip, client-hostname, server-ip, server-hostname, source-context, 
connector-id, source, event-id, internal-message-id, message-id, recipient-address, recipient-status, total-bytes, 
recipient-count, related-recipient-address, reference, message-subject, sender-address, return-path, message-info, 
directionality, tenant-id, original-client-ip, original-server-ip, custom-data

*/</QueryData>
    <QueryID>ad21fe36-414a-4db4-a427-267446c4e699</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T12:49:09.6719776-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Message Tracking: Track Message by Subject Text</QueryName>
    <QueryDescription>Track message by subject. Modify the subject text you wish to search for.</QueryDescription>
    <QueryData>/*  Track Message by Subject */

SELECT * 
INTO '[OUTFILEPATH]\TrackBySubject.CSV' 
FROM '[LOGFILEPATH]'
WHERE message-subject LIKE '%Subject Text%' 

/* 

Change "Subject Text" above to the text you wish to search for 
Searches for matches anywhere within the string.
The % functions as a wildcard for preceeding or trailing searches.


*/</QueryData>
    <QueryID>2514c413-06f0-41dc-93d4-11e84eda256c</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-05-10T14:33:02.168456-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Count all [Critical] Errors</QueryName>
    <QueryDescription>NETLOGON: Count all [Critical] Errors

Netlogon Logging must be enabled to generate these logs

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/*  NETLOGON: Count all [Critical] Errors  */

SELECT Count(*) As [Critical Errors] FROM '[LOGFILEPATH]'
WHERE TEXT LIKE '%[CRITICAL]%' 

/* Ref: http://support.microsoft.com/kb/109626 */</QueryData>
    <QueryID>b15203df-d928-4489-9043-79a2885cd63f</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Count lockout errors.</QueryName>
    <QueryDescription>Count how many login attempts which failed due to lockout.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/*  NETLOGON: Count how many login attempts which failed due to lockout  */

SELECT COUNT(*) As [Lockout Errors] FROM '[LOGFILEPATH]'
WHERE TEXT LIKE '%0XC0000234%'</QueryData>
    <QueryID>939993b8-b9ed-446c-9243-b0aa7b570b57</QueryID>
    <LogType>TEXTWORDLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find all [Critical] Errors</QueryName>
    <QueryDescription>NETLOGON: Find all [Critical] Errors.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/*  NETLOGON: Find all [Critical] Errors  */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT LIKE '%[CRITICAL]%'</QueryData>
    <QueryID>05204d78-66ab-4f73-ae4f-a6fac4a40f76</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find all can't allocate API slot errors II</QueryName>
    <QueryDescription>Finds all Cant allocate Client API Slot Errors based on error code 0xC000005E.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/*  NETLOGON: Find all Cant allocate Client API Slot Errors based on error code 0xC000005E */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT LIKE '%0xC000005E%' </QueryData>
    <QueryID>a3140eec-cc7f-425f-b12f-c030942e2cc6</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find all entries that don't return zero (success) EXPERIMENTAL</QueryName>
    <QueryDescription>NETLOGON: Find all entries that don't return zero (success) EXPERIMENTAL.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/*  NETLOGON: Find all entries that don't return zero (success) EXPERIMENTAL */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT  LIKE '%Returns%'
AND TEXT NOT  LIKE '%returns 0:%' 
AND TEXT  NOT LIKE '%Returns 0x0%' </QueryData>
    <QueryID>62224877-5b41-429d-909a-dd792a9e8024</QueryID>
    <LogType>TEXTWORDLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find can't allocate client API slot errors</QueryName>
    <QueryDescription>http://support.microsoft.com/kb/109626.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/* NETLOGON:  Find all Cant allocate Client API Slot Errors  */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT LIKE '%[CRITICAL]%' 
AND TEXT LIKE '%NlpUserValidateHigher:%' 
AND TEXT LIKE '%allocate Client API slot%' </QueryData>
    <QueryID>3fd150eb-c6b6-47e2-b869-218014bd2e25</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find failed password attempts</QueryName>
    <QueryDescription>Find all entries where a user entered the wrong password.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData> /*  NETLOGON: Find all entries where a user entered the wrong password */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT  LIKE '%0x000006A%'</QueryData>
    <QueryID>1bf21899-67f5-4208-9348-b9d348678654</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETLOGON: Find locked out accounts</QueryName>
    <QueryDescription>NETLOGON: Find all entries where a user account is locked out 

Netlogon logs only.

/* Ref: http://support.microsoft.com/kb/109626 */</QueryDescription>
    <QueryData>/* NETLOGON: Find all entries where a user account is locked out */

SELECT TOP 5000 * FROM '[LOGFILEPATH]'
WHERE TEXT  LIKE '%0XC0000234%'</QueryData>
    <QueryID>aa5751c6-f34f-4000-9eef-7901ff3e5829</QueryID>
    <LogType>TEXTWORDLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>NETMON: Bytes Per Minute</QueryName>
    <QueryDescription>Reports bytes per minute transfered in a Netmon Capture broken down per minute. Multiple files are not allowed.</QueryDescription>
    <QueryData>SELECT 	QUANTIZE(DateTime,60) AS Minute,
	SUM(FrameBytes) as [Total Bytes],
 DIV(SUM(FrameBytes), 1024) AS [Total KB], 
 DIV( DIV( SUM(FrameBytes) , 1024) , 1024) AS [Total MB] 
FROM 'c:\Captures\NetmonCapture.cap' /* &lt;- change filename and path to your capture file */
GROUP BY Minute</QueryData>
    <QueryID>b9a5ff6e-f3d2-4cef-b481-4a57443abbc6</QueryID>
    <LogType>NETMONLOG</LogType>
    <QueryCategory>CAP</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OLA: RPC/HTTP Users by Requests</QueryName>
    <QueryDescription>Outlook Anywhere users by number of requests</QueryDescription>
    <QueryData>SELECT TOP 1000 cs-username AS UserID,
 Count(*) AS Requests, 
 cs-method AS Method,
 cs(user-agent) as Client,
 cs-uri-query as RPCTarget,
 cs-uri-stem as Uri,
 sc-status As Status
 FROM '[LOGFILEPATH]'
 WHERE cs-uri-stem LIKE '%/RPC%' 
 AND UserID &lt;&gt; '' AND UserID IS NOT NULL
 AND (cs-method = 'RPC_IN_DATA' OR cs-method = 'RPC_OUT_DATA')
 GROUP BY UserID, RPCTarget, Method, Client, Uri, Status
 ORDER BY Requests Desc
 
</QueryData>
    <QueryID>b60fc466-5ff0-404a-a94e-d37c50cea054</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OLA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Budget Report [100% Exceeded]</QueryName>
    <QueryDescription>Returns all OWA requests where any budget is over 100%</QueryDescription>
    <QueryData>/* 
    Returns all OWA request where any budget is over 100%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
    These users are exceeding their budget.
*/

SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/owa%' 
AND (TO_INT(AD%) &gt; 100 OR TO_INT(CAS%) &gt; 100 OR TO_INT(AB%) &gt; 100 OR TO_INT(RPC%) &gt; 100 OR TO_INT(FC%) &gt; 100)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>d3654feb-bd74-4f68-911d-7a38e0c37b85</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:39.2350616-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Budget Report [75% Exceeded]</QueryName>
    <QueryDescription>Find all OWA requests with any budget over 75%</QueryDescription>
    <QueryData>/* 
    Returns all OWA request where any budget is over 75%
    Sorted by the total of all budgets (AD/CAS/RPC/FC/AB)
    The policy that is in effect is also listed.
*/

SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/owa%' 
AND (TO_INT(AD%) &gt; 75 OR TO_INT(CAS%) &gt; 75 OR TO_INT(AB%) &gt; 75 OR TO_INT(RPC%) &gt; 75 OR TO_INT(FC%) &gt; 75)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>875c8a2a-5c28-435d-af24-81351ee37685</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:42.5445791-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Budget Report [CSV]</QueryName>
    <QueryDescription>Creates a CSV all users per amount of total budget used where any single budget is &gt; 0% ordered by the sum of all budgets. Lists the User, Budget details, Server, query and policy applied.</QueryDescription>
    <QueryData>SELECT cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(URLUNESCAPE(cs-uri-query), 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
INTO '[OUTFILEPATH]\OWA_Budget_Top100.CSV'
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/owa%' 
AND (TO_INT(AD%) &gt; 0 OR TO_INT(CAS%) &gt; 0 OR TO_INT(AB%) &gt; 0 OR TO_INT(RPC%) &gt; 0 OR TO_INT(FC%) &gt; 0)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>8829cde2-4be2-45b0-8d59-ec2f929e3810</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-04T10:20:46.9247963-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Budget Report [Top 100 Users]</QueryName>
    <QueryDescription>Returns the Top 100 users per amount of total budget used across the sum of all budgets. Lists the User, Budget details, query and policy applied.</QueryDescription>
    <QueryData>SELECT TOP 100 cs-username AS UserID,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 1) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 1) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 1) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 1) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 1) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy:'), 0, ',') As Policy,
 EXTRACT_VALUE(cs-uri-query,'mbx') AS CAS,
	s-ip AS SERVER,
	cs-uri-query AS Query,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE  cs-uri-stem LIKE '%/owa%' 
AND (TO_INT(AD%) &gt; 0 OR TO_INT(CAS%) &gt; 0 OR TO_INT(AB%) &gt; 0 OR TO_INT(RPC%) &gt; 0 OR TO_INT(FC%) &gt; 0)
GROUP BY UserID, ADBudget, CASBudget, ABBudget, RPCBUdget, FCBudget, Policy, CAS, SERVER, Query, GMTTime
ORDER BY Total% DESC</QueryData>
    <QueryID>635f239a-fc66-442d-98d2-aee1c838afbd</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Exchange 2013 ActiveSync User Report</QueryName>
    <QueryDescription>Exchange 2013 ActiveSync User Report</QueryDescription>
    <QueryData>/*  Exchange 2013 ActiveSync User Report  */

SELECT
cs-username as UserName,
cs-uri-query as Request,
EXTRACT_VALUE(cs-uri-query,'Cmd') AS Cmd,
EXTRACT_VALUE(cs-uri-query,'User') AS User,
EXTRACT_VALUE(cs-uri-query,'DeviceId') AS DeviceId,
EXTRACT_VALUE(cs-uri-query,'DeviceType') AS DeviceType,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'DevOS:'), STRLEN(cs-uri-query)), 0, 'DevOS:'), 0, '_')  as DeviceOs,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'Error:'), STRLEN(cs-uri-query)), 0, 'Error:'), 0, '_')  as Error,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'Mbx:'), STRLEN(cs-uri-query)), 0, 'Mbx:'), 0, '_')  as MbxServer,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmRcv'), 17),5,12) as Received,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmSt'), 16),4,12) as Start,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmCmpl'), 18),6,12) as Completed,
time-taken,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmFin'), 17),5,12) as Finished,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmHang'), 18),6,12) as Hang,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'UserD.T'), STRLEN(cs-uri-query)), 0, 'UserD.T%5b%5d%3d'), 0, '%3b')  as UserDelay,
EXTRACT_PREFIX(EXTRACT_SUFFIX(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'BudgUse.T'), STRLEN(cs-uri-query)), 0, 'BudgUse.T%5b%5d%3d'), 0, '%3b')  as BudgetUsed,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'WLM.Cl%3d'), 0, '%3b') as WorkloadClassification,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'WLM.Type%3d'), 0, '%3b') as WorkloadType,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'WLM.Bal%3d'), 0, '%3b') as WorkloadBalance,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Conn%3a'), 0, '%2c') as Conn,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxConn%3a'), 0, '%2c') as [Max Conn], 
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxBurst%3a'), 0, '%2c') as [Max Burst],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Balance%3a'), 0, '%2c') as Balance ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cutoff%3a'), 0, '%2c') as Cutoff,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RechargeRate%3a'), 0, '%2c') as [Recharge Rate],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy%3a'), 0, '%2c') as Policy ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'IsServiceAccount%3a'), 0, '%2c') as IsServiceAccount 
FROM  '[LOGFILEPATH]' 
WHERE cs-uri-query LIKE '%%2cBalance%3a%' AND cs-uri-query LIKE '%user%'

</QueryData>
    <QueryID>358b0e69-1720-4575-a01e-1d0a48d0482e</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Exchange 2013 OWA action report by duration/user.</QueryName>
    <QueryDescription>Finds all requests that contain an OWA Action and lists the action name along with how long it took to process (duration). The user who made the request is also listed. This query is for Exchange 2013 OWA only.</QueryDescription>
    <QueryData>/*  Exchange 2013 OWA action report by duration and user  */

SELECT TOP 1000
QUANTIZE(TO_TIMESTAMP(date, time), 10) as [Request Time],
time-taken as Duration,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'action='), 0, '&amp;') as Action,
cs-username as USER
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%/OWA%' 
AND Action LIKE '%%' /* &lt;-- place an Action between %% to show only that action */
AND cs-uri-query LIKE '%action=%' 
ORDER BY [Request Time]  /* &lt;-- change to ORDER BY Duration DESC to sort by Duration */ </QueryData>
    <QueryID>bce790bc-5f00-41db-b4dd-7b0262615feb</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Exchange 2013 Throttling Report</QueryName>
    <QueryDescription>Exchange 2013 Throttling Report</QueryDescription>
    <QueryData> /*  Exchange 2013 ActiveSync User Throttling Report  */

SELECT
cs-username as User,
cs-uri-query as Request,
time-taken,
date,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmRcv'), 17),5,12) as Received,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmSt'), 16),4,12) as Start,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmFin'), 17),5,12) as Finished,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmCmpl'), 18),6,12) as Completed,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmHang'), 18),6,12) as Hang,
SUBSTR(SUBSTR(cs-uri-query, INDEX_OF (cs-uri-query, 'TmCnt'), 17),5,12) as Continued,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Conn%3a'), 0, '%2c') as Conn,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxConn%3a'), 0, '%2c') as [Max Conn],  
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Cutoff%3a'), 0, '%2c') as Cutoff,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RechargeRate%3a'), 0, '%2c') as [Recharge Rate],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'MaxBurst%3a'), 0, '%2c') as [Max Burst],
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Balance%3a'), 0, '%2c') as Balance ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'Policy%3a'), 0, '%2c') as Policy ,
EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'IsServiceAccount%3a'), 0, '%2c') as IsServiceAccount 
FROM  '[LOGFILEPATH]' 
WHERE cs-uri-query LIKE '%%2cBalance%3a%' AND cs-username LIKE '%user%' AND cs-uri-stem LIKE '%ActiveSync%'  /*change the user here as needed */
ORDER BY Received ASC

</QueryData>
    <QueryID>8a15f13e-b761-4434-9f4a-784cad7a6052</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Hits by User/page requested</QueryName>
    <QueryDescription>OWA Requests by IP address </QueryDescription>
    <QueryData>/* OWA: Count user hits per page accessed */

SELECT TOP 10000
cs-username AS User,  
cs-uri-stem as Page,
count(*) AS RequestCount 
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%/owa%' 
AND cs-username &lt;&gt; NULL 
GROUP BY cs-username, Page
ORDER BY cs-username DESC</QueryData>
    <QueryID>934c6ab9-8619-4350-a079-ab1b9e67b668</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-03T21:55:05.4566348-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Latency Report  [Top 100]</QueryName>
    <QueryDescription>OWA Latency for the top 100 users</QueryDescription>
    <QueryData>SELECT TOP 100 cs-username AS UserID,
	TO_INT(EXTRACT_VALUE(cs-uri-query,'prfltncy')) AS TotalLatency,
 DIV(TotalLatency, 1000) as Seconds,
	EXTRACT_VALUE(cs-uri-query,'prfrpccnt') AS RPCcount,
	EXTRACT_VALUE(cs-uri-query,'prfrpcltncy') AS RPClatency,
	EXTRACT_VALUE(cs-uri-query,'prfldpcnt') AS LDAPcount,
	EXTRACT_VALUE(cs-uri-query,'prfldpltncy') AS LDAPlatency,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
 cs-uri-query AS Query,
	cs-method AS VERB, 
	s-ip AS SERVER,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%OWA%' AND TotalLatency &gt; 100 AND Query NOT LIKE '%Attach%'
GROUP BY GMTTime, Query, HTTPstatus, UserID, Server, VERB, TotalLatency, RPCcount, RPClatency, LDAPcount, LDAPlatency
ORDER BY TotalLatency DESC</QueryData>
    <QueryID>02b08d05-ea6d-4c9d-953d-47d7722e0ab2</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Latency Report  [Top 20]</QueryName>
    <QueryDescription>OWA Top 20 requests by total latency</QueryDescription>
    <QueryData>SELECT TOP 20 cs-username AS UserID,
	cs-uri-query AS Query,
	TO_INT(EXTRACT_VALUE(cs-uri-query,'prfltncy')) AS TotalLatency,
	EXTRACT_VALUE(cs-uri-query,'prfrpccnt') AS RPCcount,
	EXTRACT_VALUE(cs-uri-query,'prfrpcltncy') AS RPClatency,
	EXTRACT_VALUE(cs-uri-query,'prfldpcnt') AS LDAPcount,
	EXTRACT_VALUE(cs-uri-query,'prfldpltncy') AS LDAPlatency,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
	cs-method AS VERB, 
	s-ip AS SERVER,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%OWA%' AND TotalLatency &gt; 100 AND Query NOT LIKE '%Attach%'
GROUP BY GMTTime, Query, HTTPstatus, UserID, Server, VERB, TotalLatency, RPCcount, RPClatency, LDAPcount, LDAPlatency
ORDER BY TotalLatency DESC</QueryData>
    <QueryID>a67c1aca-6fcb-4b9a-80d8-2f3253fefec7</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Latency Report [CSV]</QueryName>
    <QueryDescription>OWA Latency report to CSV file</QueryDescription>
    <QueryData>SELECT cs-username AS UserID,
	EXTRACT_TOKEN(cs-uri-query,0,'&amp;prfltncy=') AS Query,
	TO_INT(EXTRACT_VALUE(cs-uri-query,'prfltncy')) AS TotalLatency,
	EXTRACT_VALUE(cs-uri-query,'prfrpccnt') AS RPCcount,
	EXTRACT_VALUE(cs-uri-query,'prfrpcltncy') AS RPClatency,
	EXTRACT_VALUE(cs-uri-query,'prfldpcnt') AS LDAPcount,
	EXTRACT_VALUE(cs-uri-query,'prfldpltncy') AS LDAPlatency,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
	cs-method AS VERB, 
	s-ip AS SERVER,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime
INTO '[OUTFILEPATH]\OWALatency.csv'
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%OWA%' AND TotalLatency &gt; 100 AND Query NOT LIKE '%Attach%'
GROUP BY GMTTime, Query, HTTPstatus, UserID, Server, VERB, TotalLatency, RPCcount, RPClatency, LDAPcount, LDAPlatency
ORDER BY TotalLatency DESC</QueryData>
    <QueryID>e2a34ce7-0fa0-42c0-87c3-dad2f9c48634</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Status Codes By VDir</QueryName>
    <QueryDescription>Reports text translations and counts of all HTTP status codes by virtual directory</QueryDescription>
    <QueryData>/* Return counts all HTTP Status Codes with descriptions per Virtual Directory */
/* Caution: It is recommended to use the CSV version of this query */

SELECT
    TO_LOWERCASE(cs-uri-stem) as Vdir, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus,
     CASE HTTPstatus      
        when '200.0' then 'HttpStatusOk'                
        when '201.0' then 'HttpStatusCreated'             
        when '204.0' then 'HttpStatusNoContent' 
        when '206.0' then 'HttpStatusPartialContent' 
        when '207.0' then 'HttpStatusMultiStatus' 
	       when '241.0' then 'returned by the proxied-to CAS when it needs the proxying CAS to retry the request'
		      when '242.0' then 'returned by the proxied-to CAS for a successful ping request.'
        when '301.0' then 'HttpStatusMovedPermanently' 
        when '302.0' then 'HttpStatusRedirect' 
        when '307.0' then 'HttpStatusMovedTemporarily' 
        when '304.0' then 'HttpStatusNotModified' 
        when '400.0' then 'HttpStatusBadRequest' 
        when '400.1' then 'HttpStatusInvalidDestination' 
        when '400.2' then 'HttpStatusInvalidDepth' 
        when '400.3' then 'HttpStatusInvalidIf' 
        when '400.4' then 'HttpStatusInvalidOverwrite' 
        when '400.5' then 'HttpStatusInvalidTranslate' 
        when '400.6' then 'HttpStatusInvalidRequestBody' 
        when '400.7' then 'HttpStatusInvalidContentLength' 
        when '401.0' then 'HttpStatusUnauthorized' 
        when '401.1' then 'HttpStatusBadLogon' 
        when '401.2' then 'HttpStatusDeniedConfig' 
        when '401.3' then 'HttpStatusDeniedResource' 
        when '401.4' then 'HttpStatusDeniedFilter' 
        when '401.5' then 'HttpStatusDeniedApplication' 
        when '403.0' then 'HttpStatusForbidden' 
        when '403.1' then 'HttpStatusExecAccessDenied' 
        when '403.2' then 'HttpStatusReadAccessDenied' 
        when '403.3' then 'HttpStatusWriteAccessDenied' 
        when '403.4' then 'HttpStatusSSLRequired' 
        when '403.5' then 'HttpStatusSSL128Required' 
        when '403.6' then 'HttpStatusIPAddressReject' 
        when '403.7' then 'HttpStatusCertRequired' 
        when '403.8' then 'HttpStatusSiteAccessDenied' 
        when '403.9' then 'HttpStatusTooManyUsers' 
        when '403.11' then 'HttpStatusPasswordChange' 
        when '403.12' then 'HttpStatusMapperDenyAccess' 
        when '403.13' then 'HttpStatusCertRevoked' 
        when '403.14' then 'HttpStatusDirBrowsingDenied' 
        when '403.16' then 'HttpStatusCertInvalid' 
        when '403.17' then 'HttpStatusCertTimeInvalid' 
        when '403.18' then 'HttpStatusAppPoolDenied' 
        when '403.19' then 'HttpStatusInsufficientPrivilegeForCgi' 
        when '403.20' then 'HttpStatusPassportLoginFailure' 
        when '403.21' then 'HttpStatusSourceAccessDenied' 
        when '403.22' then 'HttpStatusInfiniteDepthDenied' 
        when '404.0' then 'HttpStatusNotFound' 
        when '404.2' then 'HttpStatusDeniedByPolicy' 
        when '404.3' then 'HttpStatusDeniedByMimeMap' 
        when '404.4' then 'HttpStatusNoHandler' 
        when '404.5' then 'HttpStatusDeniedByUrlSequence' 
        when '404.6' then 'HttpStatusDeniedByVerb' 
        when '404.7' then 'HttpStatusDeniedByFileExtension' 
        when '404.8' then 'HttpStatusHiddenSegment' 
        when '404.9' then 'HttpStatusFileAttributeHidden' 
        when '404.10' then 'HttpStatusRequestHeaderTooLong' 
        when '404.11' then 'HttpStatusUrlDoubleEscaped' 
        when '404.12' then 'HttpStatusUrlHasHighBitChars' 
        when '404.13' then 'HttpStatusContentLengthTooLarge' 
        when '404.14' then 'HttpStatus404UrlTooLong' 
        when '404.15' then 'HttpStatusQueryStringTooLong' 
        when '404.16' then 'HttpStatusStaticFileDav' 
        when '404.17' then 'HttpStatusPreconditionedHandler' 
        when '404.18' then 'HttpStatusDeniedByQueryStringSequence' 
         when '404.19' then 'HttpStatusDeniedByFilteringRule' 
        when '405.0' then 'HttpStatusMethodNotAllowed' 
        when '406.0' then 'HttpStatusNotAcceptable' 
        when '407.0' then 'HttpStatusProxyAuthRequired' 
        when '409.0' then 'HttpStatusConflict' 
        when '412.0' then 'HttpStatusPreconditionFailed' 
        when '413.0' then 'HttpStatusEntityTooLarge' 
        when '414.0' then 'HttpStatusUrlTooLong' 
        when '415.0' then 'HttpStatusUnsupportedMediaType' 
        when '416.0' then 'HttpStatusRangeNotSatisfiable' 
        when '417.0' then 'HttpStatusExpectationFailed' 
        when '423.0' then 'HttpStatusLockedError' 
        when '424.0' then 'HttpStatusFailedDependency' 
        when '441.0' then 'Returned by the proxied-to CAS when it needs to request identity'
        when '442.0' then 'Target CAS needs Timezone f-r-o-m client' 
		      when '449.0' then 'ActiveSync device blocked due to policy. Retry after sending a PROVISION command.'
        when '500.0' then 'HttpStatusServerError' 
        when '500.16' then 'HttpStatusUNCAccess' 
        when '500.19' then 'HttpStatusBadMetadata' 
        when '500.21' then 'HttpStatusHandlersModule' 
        when '500.22' then 'HttpStatusAspnetModules' 
        when '500.23' then 'HttpStatusAspnetHandlers' 
        when '500.24' then 'HttpStatusAspnetImpersonation' 
        when '501.0' then 'HttpStatusNotImplemented' 
        when '502.0' then 'HttpStatusBadGateway' 
        when '502.1' then 'HttpStatusTimeout' 
        when '502.2' then 'HttpStatusPrematureExit' 
        when '502.3' then 'HttpStatusForwarderConnectionError' 
        when '503.0' then 'HttpStatusServiceUnavailable' 
        when '503.2' then 'HttpStatusAppConcurrent' 
        when '504.0' then 'HttpStatusGatewayTimeout' 
        when '507.0' then 'HttpStatusInsufficientStorage - the user exceeded mailbox quota.'
    END as w3-status-details ,
      CASE sc-win32-status 
        WHEN 0 then ' ' 
        WHEN 2148074254 then 'handshake in progress' 
        ELSE WIN32_ERROR_DESCRIPTION(sc-win32-status) 
    END as win32-error, 
    sc-win32-status,
    	Count(*) As Hits
	FROM '[LOGFILEPATH]'
	WHERE vdir LIKE '%owa%' AND cs-username IS NOT NULL 
	Group BY vdir, HTTPstatus, w3-status-details, win32-error, sc-win32-status
	Order BY Hits, VDir</QueryData>
    <QueryID>ed57ca1d-de0b-445c-bef7-bd92cb5bc912</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Status Codes By VDir [CSV]</QueryName>
    <QueryDescription>Reports text translations and counts of all HTTP status codes by virtual directory and sends the results to csv</QueryDescription>
    <QueryData>/* Return counts all HTTP Status Codes with descriptions per Virtual Directory */
/* Caution: It is recommended to use the CSV version of this query */

SELECT
    TO_LOWERCASE(cs-uri-stem) as Vdir, 
    STRCAT(STRCAT(TO_STRING(sc-status),'.'),TO_STRING(sc-substatus)) as HTTPstatus,
     CASE HTTPstatus      
        when '200.0' then 'HttpStatusOk'                
        when '201.0' then 'HttpStatusCreated'             
        when '204.0' then 'HttpStatusNoContent' 
        when '206.0' then 'HttpStatusPartialContent' 
        when '207.0' then 'HttpStatusMultiStatus' 
	       when '241.0' then 'returned by the proxied-to CAS when it needs the proxying CAS to retry the request'
		      when '242.0' then 'returned by the proxied-to CAS for a successful ping request.'
        when '301.0' then 'HttpStatusMovedPermanently' 
        when '302.0' then 'HttpStatusRedirect' 
        when '307.0' then 'HttpStatusMovedTemporarily' 
        when '304.0' then 'HttpStatusNotModified' 
        when '400.0' then 'HttpStatusBadRequest' 
        when '400.1' then 'HttpStatusInvalidDestination' 
        when '400.2' then 'HttpStatusInvalidDepth' 
        when '400.3' then 'HttpStatusInvalidIf' 
        when '400.4' then 'HttpStatusInvalidOverwrite' 
        when '400.5' then 'HttpStatusInvalidTranslate' 
        when '400.6' then 'HttpStatusInvalidRequestBody' 
        when '400.7' then 'HttpStatusInvalidContentLength' 
        when '401.0' then 'HttpStatusUnauthorized' 
        when '401.1' then 'HttpStatusBadLogon' 
        when '401.2' then 'HttpStatusDeniedConfig' 
        when '401.3' then 'HttpStatusDeniedResource' 
        when '401.4' then 'HttpStatusDeniedFilter' 
        when '401.5' then 'HttpStatusDeniedApplication' 
        when '403.0' then 'HttpStatusForbidden' 
        when '403.1' then 'HttpStatusExecAccessDenied' 
        when '403.2' then 'HttpStatusReadAccessDenied' 
        when '403.3' then 'HttpStatusWriteAccessDenied' 
        when '403.4' then 'HttpStatusSSLRequired' 
        when '403.5' then 'HttpStatusSSL128Required' 
        when '403.6' then 'HttpStatusIPAddressReject' 
        when '403.7' then 'HttpStatusCertRequired' 
        when '403.8' then 'HttpStatusSiteAccessDenied' 
        when '403.9' then 'HttpStatusTooManyUsers' 
        when '403.11' then 'HttpStatusPasswordChange' 
        when '403.12' then 'HttpStatusMapperDenyAccess' 
        when '403.13' then 'HttpStatusCertRevoked' 
        when '403.14' then 'HttpStatusDirBrowsingDenied' 
        when '403.16' then 'HttpStatusCertInvalid' 
        when '403.17' then 'HttpStatusCertTimeInvalid' 
        when '403.18' then 'HttpStatusAppPoolDenied' 
        when '403.19' then 'HttpStatusInsufficientPrivilegeForCgi' 
        when '403.20' then 'HttpStatusPassportLoginFailure' 
        when '403.21' then 'HttpStatusSourceAccessDenied' 
        when '403.22' then 'HttpStatusInfiniteDepthDenied' 
        when '404.0' then 'HttpStatusNotFound' 
        when '404.2' then 'HttpStatusDeniedByPolicy' 
        when '404.3' then 'HttpStatusDeniedByMimeMap' 
        when '404.4' then 'HttpStatusNoHandler' 
        when '404.5' then 'HttpStatusDeniedByUrlSequence' 
        when '404.6' then 'HttpStatusDeniedByVerb' 
        when '404.7' then 'HttpStatusDeniedByFileExtension' 
        when '404.8' then 'HttpStatusHiddenSegment' 
        when '404.9' then 'HttpStatusFileAttributeHidden' 
        when '404.10' then 'HttpStatusRequestHeaderTooLong' 
        when '404.11' then 'HttpStatusUrlDoubleEscaped' 
        when '404.12' then 'HttpStatusUrlHasHighBitChars' 
        when '404.13' then 'HttpStatusContentLengthTooLarge' 
        when '404.14' then 'HttpStatus404UrlTooLong' 
        when '404.15' then 'HttpStatusQueryStringTooLong' 
        when '404.16' then 'HttpStatusStaticFileDav' 
        when '404.17' then 'HttpStatusPreconditionedHandler' 
        when '404.18' then 'HttpStatusDeniedByQueryStringSequence' 
         when '404.19' then 'HttpStatusDeniedByFilteringRule' 
        when '405.0' then 'HttpStatusMethodNotAllowed' 
        when '406.0' then 'HttpStatusNotAcceptable' 
        when '407.0' then 'HttpStatusProxyAuthRequired' 
        when '409.0' then 'HttpStatusConflict' 
        when '412.0' then 'HttpStatusPreconditionFailed' 
        when '413.0' then 'HttpStatusEntityTooLarge' 
        when '414.0' then 'HttpStatusUrlTooLong' 
        when '415.0' then 'HttpStatusUnsupportedMediaType' 
        when '416.0' then 'HttpStatusRangeNotSatisfiable' 
        when '417.0' then 'HttpStatusExpectationFailed' 
        when '423.0' then 'HttpStatusLockedError' 
        when '424.0' then 'HttpStatusFailedDependency' 
        when '441.0' then 'Returned by the proxied-to CAS when it needs to request identity'
        when '442.0' then 'Target CAS needs Timezone f-r-o-m client' 
		      when '449.0' then 'ActiveSync device blocked due to policy. Retry after sending a PROVISION command.'
        when '500.0' then 'HttpStatusServerError' 
        when '500.16' then 'HttpStatusUNCAccess' 
        when '500.19' then 'HttpStatusBadMetadata' 
        when '500.21' then 'HttpStatusHandlersModule' 
        when '500.22' then 'HttpStatusAspnetModules' 
        when '500.23' then 'HttpStatusAspnetHandlers' 
        when '500.24' then 'HttpStatusAspnetImpersonation' 
        when '501.0' then 'HttpStatusNotImplemented' 
        when '502.0' then 'HttpStatusBadGateway' 
        when '502.1' then 'HttpStatusTimeout' 
        when '502.2' then 'HttpStatusPrematureExit' 
        when '502.3' then 'HttpStatusForwarderConnectionError' 
        when '503.0' then 'HttpStatusServiceUnavailable' 
        when '503.2' then 'HttpStatusAppConcurrent' 
        when '504.0' then 'HttpStatusGatewayTimeout' 
        when '507.0' then 'HttpStatusInsufficientStorage - the user exceeded mailbox quota.'
    END as w3-status-details ,
      CASE sc-win32-status 
        WHEN 0 then ' ' 
        WHEN 2148074254 then 'handshake in progress' 
        ELSE WIN32_ERROR_DESCRIPTION(sc-win32-status) 
    END as win32-error, 
    sc-win32-status,
    	Count(*) As Hits
 INTO '[OUTFILEPATH]\OWAStatusCodeReportByVDir.CSV'
	FROM '[LOGFILEPATH]'
	WHERE vdir LIKE '%owa%' AND cs-username IS NOT NULL 
	Group BY vdir, HTTPstatus, w3-status-details, win32-error, sc-win32-status
	Order BY Hits, VDir</QueryData>
    <QueryID>b9e5f0ee-5fbc-4b56-91bd-998f5f80ab02</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>OWA: Top 20 OWA Users</QueryName>
    <QueryDescription>Find the top 20 OWA users by total number of requests</QueryDescription>
    <QueryData>SELECT TOP 20 cs-username AS UserID, 
	cs(User-Agent) AS Application, 
	cs-uri-stem AS Vdir,
	c-ip AS CLIENT,
	cs-method,
	Count(*)
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%OWA%'
GROUP BY UserID, Application, Vdir, Client, cs-method
ORDER BY COUNT(*) DESC</QueryData>
    <QueryID>ad83693a-1fec-48e3-aa1c-8e1941e706da</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>OWA</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Page Hits per Single IP</QueryName>
    <QueryDescription>All pages/hits made by a single client IP address</QueryDescription>
    <QueryData>/* All pages/hits by a given client IP */

select cs-uri-stem, 
count(cs-uri-stem) as requestcount 
from '[LOGFILEPATH]' 
where c-ip = '000.000.000.000'
 group by cs-uri-stem order by count(cs-uri-stem) desc</QueryData>
    <QueryID>cade0ee3-14f0-4f68-b7cf-4263cd83ab34</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Password Error Report</QueryName>
    <QueryDescription>Finds and counts passwords errors such as password expired, user doesn't exist, account restriction etc.</QueryDescription>
    <QueryData>/* Find password releated issues such as account lockouts etc */
 
SELECT

   TO_LOWERCASE (cs-username)    AS User,
   COUNT(*)                      AS Hits,
   cs(User-Agent)                AS User-Agent,
   sc-win32-status               AS ErrorCode,

   WIN32_ERROR_DESCRIPTION(sc-win32-status) as Reason
   
FROM '[LOGFILEPATH]'  
WHERE sc-win32-status = '1317' or /* user doesn't exist */
      sc-win32-status = '1326' or /* wrong password     */
      sc-win32-status = '1330' or /* password expired   */
      sc-win32-status = '1327' or /* account restriction*/
      sc-win32-status = '1331' or /* account disabled   */
      sc-win32-status = '1909'    /* account locked out */
      
GROUP BY User, User-Agent, ErrorCode, Reason
ORDER BY Hits DESC
       

/* WIN32_ERROR_DESCRIPTION( win32ErrorCode &lt;INTEGER&gt; ) */</QueryData>
    <QueryID>4d7f65b1-830b-450d-a7ed-92e90bac7d3c</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>MISC</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Powershell: Find 500 Errros</QueryName>
    <QueryDescription>Returns a count of all errors for requests to /Powershell</QueryDescription>
    <QueryData>/* Count and List all Powershell Errors by URI */

SELECT time as Time, sc-status as StatusCode, 
sc-win32-status as Win32Code, cs-uri-stem as URI, 
COUNT(sc-status) FROM '[LOGFILEPATH]' WHERE cs-uri-stem LIKE '%/PowerShell%' AND 
sc-status &gt; 499 GROUP BY Time, Date, StatusCode, Win32Code, URI ORDER BY Time DESC</QueryData>
    <QueryID>d684ade1-fbf9-4742-bb04-f3b54cba5c9b</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>PS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Powershell: Hits (HTTP 500)</QueryName>
    <QueryDescription>All requests made to /PowerShell that result in a 500 error</QueryDescription>
    <QueryData>/* Count and List all Powershell Errors by URI */

SELECT time as Time, cs-username, sc-status as StatusCode, 
sc-win32-status as Win32Code, cs-uri-stem as URI, 
COUNT(sc-status) 
FROM '[LOGFILEPATH]' 
WHERE cs-uri-stem 
LIKE '%/PowerShell%' AND sc-status &gt; 499 
GROUP BY cs-username, Time, Date, StatusCode, Win32Code, URI ORDER BY Time DESC</QueryData>
    <QueryID>c2181028-833a-4833-bbf5-e1ff84716b75</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>PS</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>REGISTRY: Find Registry Query Field Names</QueryName>
    <QueryDescription>Finds Registry Query Field Names for use when writing your own registry queries</QueryDescription>
    <QueryData>/*  Get Registry Query Field Names - Does not return results */

SELECT TOP 0 *
FROM HKCU\SOFTWARE\MICROSOFT 
WHERE LastWriteTime &gt;= SUB(SYSTEM_TIMESTAMP(), TIMESTAMP('2999-02-10', 'yyyy-MM-dd')) 

</QueryData>
    <QueryID>b3df3a58-c844-4a65-9006-128174330633</QueryID>
    <LogType>REGLOG</LogType>
    <QueryCategory>MISC</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>REGISTRY: Writes Since Previous Date</QueryName>
    <QueryDescription>Find all Key\Values written to since the chosen date</QueryDescription>
    <QueryData>/*  Find Registry values that have been writtin to since ??? */

SELECT Path, ValueName, Value
FROM \\COMPUTER\HKCU\SOFTWARE\MICROSOFT /* &lt;- \\Computer\HIVE\KEY */
WHERE LastWriteTime &gt;= SUB(SYSTEM_TIMESTAMP(), TIMESTAMP('0000-02-10', 'yyyy-MM-dd')) /* &lt;- date since modified */</QueryData>
    <QueryID>665b6b82-99d5-44ac-9433-3da4bd94421b</QueryID>
    <LogType>REGLOG</LogType>
    <QueryCategory>MISC</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Breakdown of MAPI clients by percentage</QueryName>
    <QueryDescription>Enter description.</QueryDescription>
    <QueryData>/*  Clients accessing Exchange 2010/2013 via MAPI broken down by percentage [Chartable] */

SELECT TOP 1000 TO_LOWERCASE(Client-Software) as Software, Client-Software-Version,
TO_REAL(SUBSTR(TO_STRING(MUL(PROPCOUNT(*), 100.0)), 0, 5)) as Percent
FROM '[LOGFILEPATH]'
WHERE Software IS NOT NULL
GROUP BY client-software, Client-Software-Version
ORDER BY Percent DESC

</QueryData>
    <QueryID>2d854494-f23d-4de1-ad8c-bd6910abd0f1</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Breakdown of MAPI clients by percentage/version</QueryName>
    <QueryDescription>Finds all client applications that are accessing Exchange CAS via MAPI and reports them by percentage per client and client version. 

This is a chart friendly query. Just press F6 when the query has completed.</QueryDescription>
    <QueryData>/*  Clients accessing Exchange 2010/2013 via MAPI broken down by percentage [Chartable] */

SELECT TOP 1000 TO_LOWERCASE(Client-Software) as Software, Client-Software-Version,
TO_REAL(SUBSTR(TO_STRING(MUL(PROPCOUNT(*), 100.0)), 0, 5)) as Percent
FROM '[LOGFILEPATH]'
WHERE Software IS NOT NULL
GROUP BY client-software, Client-Software-Version
ORDER BY Percent DESC

</QueryData>
    <QueryID>c32aa080-30c7-49b6-861a-fba052f2fd01</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Client connect/disconnect report by hour</QueryName>
    <QueryDescription>Counts all connects and disconnects in 1 hour intervals</QueryDescription>
    <QueryData>/* RPCA: Count client connects/disconnects per hour */
SELECT Count (*) as Total, 
QUANTIZE(TO_TIMESTAMP(EXTRACT_PREFIX(EXTRACT_SUFFIX([#Fields: date-time], 0, 'T'), 0, '.'), 'hh:mm:ss'), 3600) As Time, 
Operation 
FROM '[LOGFILEPATH]' 
WHERE Operation LIKE '%Connect%' 
Group By Time, Operation 
Order by Time ASC
</QueryData>
    <QueryID>10bae9d1-b97e-4ef8-928d-98f41a8d79ca</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Connect/Disconnect Report (10 Second Intervals)</QueryName>
    <QueryDescription>Counts all RPC Client Access Service MAPI connect/disconnect operations across 10 second intervals.</QueryDescription>
    <QueryData>/* RPC Client Access: Find all connects and disconnects; count per 10 second interval */

SELECT Count (*) as Total, TO_STRING(QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX
(EXTRACT_SUFFIX([#Fields: date-time], 0, 'T'), 0, '.'), 'hh:mm:ss')), 10), 'hh:mm:ss') As Time, 
operation 
FROM '[LOGFILEPATH]' 
WHERE operation LIKE '%Connect%' 
GROUP BY Time,operation Order by time ASC</QueryData>
    <QueryID>42eb24f5-4a6a-41b1-ac1d-cd20734a4fcc</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Count RPC backoff errors per user</QueryName>
    <QueryDescription>Count RPC backoff errors per user. </QueryDescription>
    <QueryData>/* Count RPC backoff errors per user */

Select Count(*) as RPCErrors, client-name
FROM '[LOGFILEPATH]' 
WHERE TO_STRING(rpc-status) LIKE '%0x6bb%'
GROUP BY client-name
ORDER BY RPCErrors DESC

This query is intended for Exchange RPC Client Access Logs</QueryData>
    <QueryID>9964abd5-29be-4997-8819-90ede911cb13</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Count Various Errors</QueryName>
    <QueryDescription>Aggregate and count various RPCA Errors in RPC Client Access Server logs. </QueryDescription>
    <QueryData>/*  Aggregate and count various RPCA Errors and Export to CSV */

SELECT  
QUANTIZE(TO_TIMESTAMP(EXTRACT_PREFIX(EXTRACT_SUFFIX([#Fields: date-time], 0, 'T'), 0, '.'), 'hh:mm:ss'), 3600) As Time, 
client-software,  protocol, REPLACE_STR(failures, ',', ' ') as Failure,
MyServerTooBusy as [ServerTooBusy], MyAccessDenied as [Access Denied], MyMaxSubmission as [Max MsgSubmission], MyNoSupport as [No Support], 
MyRopExecution AS [Rop Execution], MyStorage As Storage, MyObjectNotFound as [Object NotFound], MyServerUnavailable as ServerUnavailable, 
MyConnectionFailedTransientException as ConnectionFailedTransientException, MyMapiExceptionLogonFailed as MapiExceptionLogonFailed,
MySessionDeadException as SessionDeadException, MyMapiExceptionNotEncrypted as MapiNotEncrypted

USING 
STRCNT(failure, 'ServerTooBusyException') as Server2Busy,
STRCNT(failure, 'AccessDeniedException') As AccDenied, 
STRCNT(failure, 'MessageSubmissionExceeded') As MaxSubmission, 
STRCNT(failure, 'NoSupportException') As NoSupportErr, 
STRCNT(failure, 'RopExcecutionException') As RopExecuteErr, 
STRCNT(failure, 'StoragePermanentException') As StoragePermanentErr,
STRCNT(failure, 'ObjectNotFoundException') As ObjectNotFoundErr,
STRCNT(failure, 'ServerUnavailable') As ServerUnavailableErr,
STRCNT(failure, 'ConnectionFailedTransientException') As ConnectionFailedTransientErr,
STRCNT(failure, 'MapiExceptionLogonFailed') As MapiExceptionLogonFailedErr,
STRCNT(failure, 'SessionDeadException') As SessionDeadErr,
STRCNT(failure, '[MapiExceptionNotEncrypted]') As MapiExceptionNotEncryptedErr,

  CASE Server2Busy
    WHEN 1 THEN SUM (1)
    ELSE 0
  END AS MyServerTooBusy,

  CASE AccDenied 
    WHEN 1 THEN SUM (1)
    ELSE 0
  END AS MyAccessDenied,

  CASE MaxSubmission 
    WHEN 1 THEN SUM (1)
    ELSE 0 
  END AS MyMaxSubmission,
  
  CASE NoSupportErr 
    WHEN 1 THEN SUM (1)
    ELSE 0 
  END AS MyNoSupport,

  CASE RopExecuteErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyRopExecution,

  CASE StoragePermanentErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyStorage,

  CASE ObjectNotFoundErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyObjectNotFound,


  CASE ConnectionFailedTransientErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyConnectionFailedTransientException,
  
  CASE MapiExceptionLogonFailedErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyMapiExceptionLogonFailed,
  
  CASE SessionDeadErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MySessionDeadException,

  CASE MapiExceptionNotEncryptedErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyMapiExceptionNotEncrypted,

  CASE ServerUnavailableErr
    WHEN 1 THEN SUM(1)
    ELSE 0
  END AS MyServerUnavailable
  

INTO '[OUTFILEPATH]\RCAErrors.CSV'   
FROM '[LOGFILEPATH]'   
WHERE failures IS NOT NULL
GROUP BY Time, failures, client-software, protocol, processing-time

/* Filename, RowNumber, #Fields: date-time, seq-number, client-name, organization-info, client-software, client-software-version, client-mode, client-ip, 
server-ip, protocol, application-id, operation, operation-specific, failures */ 
</QueryData>
    <QueryID>6d2630c6-86c9-49ae-a576-ff95ceb049d0</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: MAPI client report by percentage</QueryName>
    <QueryDescription>Finds all client applications that are accessing Exchange CAS via MAPI and reports them by percentage per client. 

This is a chart friendly query. Just click F6 when the query has completed.</QueryDescription>
    <QueryData>/* Exchange RPC Client Access logs, lists MAPI clients connecting to CAS by percentage */

SELECT TOP 1000  Client-Software,
MUL(PROPCOUNT(*), 100.0) as Percent
FROM '[LOGFILEPATH]'
GROUP BY  client-software 
ORDER BY Percent DESC

</QueryData>
    <QueryID>612b28cc-8dbe-4478-9eaf-92ec2c5c0fad</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>RPCA: Timed ROPs Report</QueryName>
    <QueryDescription>Attempts to time individual ROPS between CAS and MBX Servers/Roles</QueryDescription>
    <QueryData>/*  Calculates milliseconds per ROP in RPC Client Access Logs 
    ROP logging must be enabled in order to run this query 
    See "Diagnostics" here: 
    http://blogs.msdn.com/b/stephen_griffin/archive/2011/10/19/random-mapi-e-call-failed-errors-with-exchange-2010.aspx 
    for more details on enabling ROP logging 
*/

SELECT TOP 1000 
[#Fields: date-time], processing-time,
STRCNT(operation-specific, '&gt;') as RopsIn,
STRCNT(operation-specific, '&lt;') as RopsOut,
ADD(
    ADD(MUL(TO_REAL(SUBSTR(processing-time, 0, 2)), 3600000), MUL(TO_REAL(SUBSTR(processing-time, 3, 2)), 60000)),
    ADD(MUL(TO_REAL(SUBSTR(processing-time, 6, 2)), 1000), MUL(TO_REAL(SUBSTR(processing-time, 9, 3)), 1)    )
   ) as [MS All Rops],
DIV([MS All Rops], ADD(RopsiN, RopsOut) ) as [MS Per Rop],
operation-specific, session-id, seq-number, client-name, client-software,
client-software-version, client-mode, client-ip, server-ip, protocol, operation, 
rpc-status,  failures
FROM '[LOGFILEPATH]'
WHERE [MS All Rops] IS NOT NULL
AND  Client-Name LIKE '%%'
AND Operation NOT  LIKE '%Disconnect%' 

/* Available Fields: Filename, RowNumber, [#Fields: date-time], session-id, seq-number, client-name, organization-info, client-software, 
client-software-version, client-mode, client-ip, server-ip, protocol, application-id, operation, rpc-status, 
processing-time, operation-specific, failures */

/* NOTE: 'TOP X' limits the number of records returned to conserve resources, if you need more than 100k records increase that number or send the output to CSV */</QueryData>
    <QueryID>968081ce-865d-44dc-8395-c371082688c2</QueryID>
    <LogType>EELLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>TextLine: Download a web page from the Internet</QueryName>
    <QueryDescription>Downloads the HTML in a web page on the Internet</QueryDescription>
    <QueryData>/*  Demo Query: Download a web page from the internet line by line  */

SELECT Text FROM 'http://websitenamegoeshere.com'
WHERE Text IS NOT NULL
 </QueryData>
    <QueryID>759e1d7c-c6d7-4b6f-89f1-25380f751001</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T22:55:01.5579945-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>TextLine: Sample Batch Query 1</QueryName>
    <QueryDescription>Sample batch query 1</QueryDescription>
    <QueryData>/*  Sample Batch Query: Top Ten Text Lines  */

SELECT TOP 10 * FROM '[LOGFILEPATH]'</QueryData>
    <QueryID>3f755221-91bb-40a8-bb87-eea499efee87</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-06T10:29:51.4939684-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>TextLine: Sample Batch Query 2</QueryName>
    <QueryDescription>Sample batch query 2</QueryDescription>
    <QueryData>/*  Sample Batch Query: Top Ten Text Lines  */

SELECT TOP 5 * FROM '[LOGFILEPATH]'</QueryData>
    <QueryID>b1a9229b-882c-45a5-a110-745d2728e727</QueryID>
    <LogType>TEXTLINELOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-06T10:29:39.3332631-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>Throttling: Find Delays</QueryName>
    <QueryDescription>Finds all users with "Delay" in the response, then reports all relevant throttling indicators </QueryDescription>
    <QueryData>/* Finds all users with "Delay" in the response, then reports all relevant throttling indicators */

SELECT TOP 5 cs-username AS UserID, 
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'UserDelay:'), 0, ';') AS UserDelay,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'UserDelayCnt:'), 0, ';') AS UserDelayCount,
 REPLACE_STR(EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'SoapAction='), 0, ';'), 'm:', '') As SoapAction,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AD:'), 0, ',') As ADBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'CAS:'), 0, ',') As CASBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'AB:'), 0, ',') As ABBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'RPC:'), 0, ',') As RPCBudget,
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'FC:'), 0, ',') As FCBudget,
 SUBSTR(ADBudget, ADD( LAST_INDEX_OF(ADBudget, '/') , 1) , 2) AS AD%,
 SUBSTR(CASBudget, ADD( LAST_INDEX_OF(CASBudget, '/') , 1) , 2) AS CAS%,
 SUBSTR(ABBudget, ADD( LAST_INDEX_OF(ABBudget, '/') , 1) , 2) AS AB%,
 SUBSTR(RPCBudget, ADD( LAST_INDEX_OF(RPCBudget, '/') , 1) , 2) AS RPC%,
 SUBSTR(FCBudget, ADD( LAST_INDEX_OF(FCBudget, '/') , 1) , 2) AS FC%,
 ADD(ADD(ADD(TO_INT(AD%), TO_INT(CAS%)), ADD(TO_INT(AB%), TO_INT(RPC%))), TO_INT(FC%)) As Total%,
 	cs(User-Agent) AS DeviceType, 
 EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query,0,'Policy:'), 0, ',') AS Policy,
	cs-uri-stem AS Vdir,
	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS HTTPstatus,
	s-ip AS SERVER,
	c-ip AS CLIENT,
	STRCAT(TO_STRING(date, 'yyyy-MM-dd'), STRCAT('  ', TO_STRING(time, 'hh:mm:ss'))) AS GMTTime,
 cs-uri-query

FROM '[LOGFILEPATH]'
WHERE cs-uri-query LIKE '%Delay%' 
GROUP BY GMTTime, HTTPstatus, UserID, Server, DeviceType, UserDelay, Vdir, Client, cs-uri-query
ORDER BY UserDelay, GMTTime, UserID, client, deviceType, Vdir, UserDelay, HTTPstatus DESC</QueryData>
    <QueryID>67a91771-ed35-494a-a57b-7d3e5cd3c3af</QueryID>
    <LogType>IISW3CLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>true</IsFavorite>
    <DateModified>2013-06-01T21:51:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>XML: Books Sample 1</QueryName>
    <QueryDescription>A simple query demonstrating how to query an XML file.</QueryDescription>
    <QueryData>/*  Simple XML Query using Books.XML which is located in the LPS Samples folder  */

SELECT TOP 10 * FROM 'Samples/Books.XML'</QueryData>
    <QueryID>e3dc9e56-ca79-4050-85b1-29e85c968cf4</QueryID>
    <LogType>XMLLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T22:59:14.2117699-05:00</DateModified>
  </LPQuery>
  <LPQuery>
    <QueryName>XML: Books Sample 2</QueryName>
    <QueryDescription>A simple query demonstrating how to query an XML file.</QueryDescription>
    <QueryData>/*  Simple XML example. Find all authors with the letter a in their name  */

SELECT TOP 10 * FROM 'Samples\Books.XML'
WHERE Author LIKE '%a%' </QueryData>
    <QueryID>1dff1e51-6cb9-486f-9de5-7d6de0afe4e1</QueryID>
    <LogType>XMLLOG</LogType>
    <QueryCategory>ALL</QueryCategory>
    <IsFavorite>false</IsFavorite>
    <DateModified>2013-06-05T23:00:22.5590843-05:00</DateModified>
  </LPQuery>
</ArrayOfLPQuery>